Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
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
@rammuthiah Please see the screen shot below:
If this resolves your issue, please like and accept it as a solution.
May I know the script you are using so far
@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.