Issue using Total Qualifier and Dimension Limit in Stacked Bar Chart

[m_perreault]

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

[sunny_talwar · Accepted solution]

Try this

Sum(Revenue)/Aggr(NODISTINCT Sum(Revenue), [CP Month Year])

 

[m_perreault]

Works perfectly thank you Sunny! Are you able to explain a little what this is doing differently than the Total qualifier?

[m_perreault]

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

[sunny_talwar]

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 🙂

[Anonymous]

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