- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
% based on Program Aggregation of status by month
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...
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@rammuthiah Please see the screen shot below:
If this resolves your issue, please like and accept it as a solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May I know the script you are using so far
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.