Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to calculate a weighted subtotal for the Total column.
Calculation for Weight:
sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)
Percent:
sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk])
Total:
Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk])))
I would like to be able to drop the Weight and Percent columns and just use the Total Column. Market 1 total should be $873. Market 2 should be $861, or the sum of the percent column. I know I'm close....
Market | County | TotalMM | Bnchmrk | Weight | Percent | Total |
1 | Market Total | 2239 | 100.00% | $5,235 | ||
1 | A | 276 | $846 | 12.33% | $104 | $846 |
1 | B | 590 | $881 | 26.35% | $232 | $881 |
1 | C | 58 | $891 | 2.59% | $23 | $891 |
1 | D | 392 | $843 | 17.51% | $148 | $843 |
1 | E | 392 | $891 | 17.51% | $156 | $891 |
1 | F | 531 | $883 | 23.72% | $209 | $883 |
2 | Market Total | 2229 | 100.00% | $2,574 | ||
2 | G | 454 | $860 | 20.37% | $175 | $860 |
2 | H | 1116 | $874 | 50.07% | $437 | $874 |
2 | I | 659 | $840 | 29.56% | $248 | $840 |
Then in that case... try this as Total is redundant
Avg(Aggr(
Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name>TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk]))),
Market, County))
Can you share a sample to look into? Or may be try like:
= Sum(Aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL<Market> TotalMM)*([Bnchmrk]), Market))
Can you also show what should be your expected output.
May be this
Avg(Aggr(
Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name>TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk]))),
Market))
Thanks Vishwarath, I tried this formula, but it gave me all $0 for the total Column. Here is what it should look like.
Market | County | TotalMM | Bnchmrk | Weight | Percent | Total |
1 | Market Total | 2239 | 100.00% | $873 | ||
1 | A | 276 | $846 | 12.33% | $104 | $846 |
1 | B | 590 | $881 | 26.35% | $232 | $881 |
1 | C | 58 | $891 | 2.59% | $23 | $891 |
1 | D | 392 | $843 | 17.51% | $148 | $843 |
1 | E | 392 | $891 | 17.51% | $156 | $891 |
1 | F | 531 | $883 | 23.72% | $209 | $883 |
2 | Market Total | 2229 | 100.00% | $861 | ||
2 | G | 454 | $860 | 20.37% | $175 | $860 |
2 | H | 1116 | $874 | 50.07% | $437 | $874 |
2 | I | 659 | $840 | 29.56% | $248 | $840 |
Thanks Sunny. this just gave me a total of the County Benchmarks. They counties were null.
How about this
Avg(TOTAL <Market> Aggr(
Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name>TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk]))),
Market, County))
Try this:
= Sum(TOTAL <Market, County_name> Aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL<Market> TotalMM)*([Bnchmrk]), Market))
Please also try Sunny's expression. If you still do not see what you expected, please do share your qvw for us to look into.
This is closer. It populated all the counties, but it gave me all the same number. $835.
I figured it out. Needed to add County to the first part. THANK YOU so much for your help!!!!!
Avg(TOTAL <Market,County_Name> Aggr(
Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name>TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk]))),
Market, County))
Then in that case... try this as Total is redundant
Avg(Aggr(
Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name>TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk]))),
Market, County))