Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table as follows
Date | Name | Denominator | Category | Score |
Sep | A | 50332 | None | 3102 |
Sep | A | 50332 | Min | 2416 |
Nov | A | 47030 | Medium | 2338 |
Nov | A | 47030 | Max | 1710 |
Sep | B | 43792 | None | 629 |
Sep | B | 43792 | Min | 567 |
Sep | B | 43792 | Max | 552 |
Mar | C | 33036 | None | 434 |
Mar | C | 33036 | Min | 497 |
Mar | C | 33036 | Medium | 529 |
Mar | C | 33036 | Max | 890 |
Apr | C | 39322 | None | 841 |
Apr | C | 39322 | Min | 1493 |
Apr | C | 39322 | Medium | 1702 |
Apr | C | 39322 | Max | 1521 |
This is the result of a crosstable since my categories were previously different columns in my source file. That's why the denominator, date, and name are duplicated across rows.
I have calculated a field in my script - ((Score)/(denominator)) *100as Rate_Calculated so that a rate is calculated in every row. On the front end, I do sum(Rate_calculated)/count(distinct Date) to get the averge rate for each name.
How can I find the average Rate calculated for all Names across dates and categories? Also, how can I find the largest and smallest rates?
I've tried Avg(Total Rate_calculated), sum({<Name=>}Rate_Calculated)/count(distinct Date) but they don't seem to work.
Need this urgently, so appreciate any help!
May be try this:
Avg(Aggr(Sum(Rate_calculated)/Count(DISTINCT Date), name))
For min and max, may be this:
Min(Aggr(Sum(Rate_calculated)/Count(DISTINCT Date), name))
Max(Aggr(Sum(Rate_calculated)/Count(DISTINCT Date), name))
Thanks so much! Follow up question - How can I exclude some rows from being counted in this calculation?
I have a flag set up in my script called Exclude. Based on some conditions, it's 1 if the row should be excluded from this calculation or 0 if it should be included. I've done - Avg(Aggr(Sum({<Exclude={'0'}>}[Rate_Calculated])/Count(DISTINCT Date), name)) but I'm not getting the right answer.
Also, how can I compare the rate of 1 name against this overall average? I need to colour these rows based on whether they are below or above the overall average, but when I add the name dimension to the table, your formula doesn't stay static.
1st requirement;
Avg({<Exlcuded = {0}>}Aggr(Sum({<Exlcuded = {0}>}Rate_calculated)/Count({<Exlcuded = {0}>}DISTINCT Date), name))
For your 2nd requirement, I am not sure how you want to do the comparison. Can you share more information? May be give a sample with expected output. Would be very easy to help this way.
I guess, your 2nd requirement don't need AGGR function because you are adding chart object which using name as dimension. One more thing, you already calculated your rate in the script why you are doing both avg function and Sum/Count functions. Please find the below:
Dimension:
Name
Expression:
Avg for all : avg(Rate_Calculated)
Min for all : min(Rate_Calculated)
Max for all : max(Rate_Calculated)
avg for None : avg({<Category={"None"}>}Rate_Calculated)
avg for Min : avg({<Category={"Min"}>}Rate_Calculated)
avg for Max : avg({<Category={"Max"}>}Rate_Calculated)
avg for Medium : avg({<Category={"Medium"}>}Rate_Calculated)