Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have data structured as below
Month - Year, Region, Sales
CP Month Year | Region | Sum(Revenue) |
Jan-2018 | Region 1 | 8,653,078.21 |
Jan-2018 | Region 2 | 0.00 |
Jan-2018 | Region 3 | 70,971,372.42 |
Jan-2018 | Region 4 | 81,543,637.25 |
Jan-2018 | Region 5 | 18,371,222.42 |
Feb-2018 | Region 1 | 8,665,560.37 |
Feb-2018 | Region 2 | 0.00 |
Feb-2018 | Region 3 | 97,881,469.02 |
Feb-2018 | Region 4 | 81,190,570.85 |
Feb-2018 | Region 5 | 19,253,011.57 |
I have 107 different Regions so I am trying to create a stacked bar chart with one dimension CP Month Year and another dimension Region and the measure is sum(Revenue)/Sum(Total <[CP Month Year]> Revenue). I only want to show the top 4 regions per Month Year so I put a Dimensional Limit on Revenue.
When I do this in a grouped bar chart with a dimensional limit or a table with a dimensional limit or a stacked bar chart with NO dimensional limit my chart looks correct however once I put the dimensional limit in the stacked bar chart the chart is showing erroneous values. Attached is data and QVF if anyone is able to take a look that would be very helpful.
Stacked Bar Chart No Dimensional Limit
Grouped Bar Chart Dimensional Limit on Region
Table with Dimensional Limit
Stacked Bar Chart with Dimensional Limit
Thanks,
Mark
Try this
Sum(Revenue)/Aggr(NODISTINCT Sum(Revenue), [CP Month Year])
Works perfectly thank you Sunny! Are you able to explain a little what this is doing differently than the Total qualifier?
Also I apologize when I try and accept your answer as a solution I am getting a page not found error message.
@Anonymous ,@Anonymous can you please mark Sunny's answer as correct
I am not entirely sure, seems to me like a bug... but what I know is that TOTAL <Dim> is equivalent (there are exceptions to this) of Aggr(NODISTINCT ...., Dim).... so when one doesn't work, I usually try the other to see if that works. That is exactly what I did in this case 🙂
Hi @m_perreault,
Thanks for reaching out about the error, and apologies for the inconvenience. The community is currently experiencing an issue that's causing the Page Not Found error to appear, and we are investigating it with our platform vendor.
Best,
Jeremy