% based on Program Aggregation of status by month

[rammuthiah]

Hi,

I have month, status, of program and i want to calculate % of it

For Eg. 

Month	Status	Program Count
Jan	Red	10
Jan	Yellow	10
Jan	Green	20
Feb	Red	5
Feb	Yellow	20
Feb	Green	15

I need output like 

Red : count of Red Program/Month ->10/40 -> 25% for Jan, 

Yellow : count of Yellow Program/Month -> 10/40 -> 25% for Jan, 

Green : Count of Green Program/ Month -> 20/40 -> 50% for Jan, 

Likewise...

[justISO · Accepted solution]

Hi, it depends on where you want to calculate this, in script level or report level, but in report you can aggregate sum of program count per month for this:

sum([Program Count]) / aggr(nodistinct sum([Program Count]), Month)

In script you can add additional column with same aggregated program count by month (and later do the calculation). Something like this

...

LEFT JOIN (your_main_data_table)

LOAD Month

sum([Program Count]) as aggr_month

RESIDENT your_main_data_table

GROUP BY Month

[sidhiq91]

@rammuthiah Please see the screen shot below:

If this resolves your issue, please like and accept it as a solution.

[rammuthiah]

May I know the script you are using so far

[sidhiq91]

@rammuthiah  There was no any script that was used. Please see below for the script in the load script editor

NoConcatenate
Temp:
Load * inline [
Month, Status, Program Count
Jan, Red, 10
Jan, Yellow, 10
Jan, Green, 20
Feb, Red, 5
Feb, Yellow, 20
Feb, Green, 15
];

Exit Script;

And then the expression is what you see in the table.