Set Expression issue for sum - different result in visualizations

[Mako]

Hello together, apologies if this has been asked before, but I have searched the forum and help for days and could not find a solution/answer to my issue yet.

I am creating an application in my organization to analyze cost of product. In our company, we do this by measuring certain types of cost vs. the revenue of the products (e.g., "relative to our product revenue, we pay 3.1% on reworks").  We do not limit this to just the products that have incurred a certain cost type, but versus the revenue of all products in e.g. a certain brand.

The data model I have set up consists of one table with the cost, each linked to a cost category ID as well as an SKU ID and date, and one table with the revenues for SKU IDs with dates. In addition, I have two simple tables with master data about SKUs and the cost categories.

It has the following tables/fields, including one synthetic key for the SKU ID/Date pair:

Table "Revenues":

$Syn 1:

      - SKU ID

      - Date (always a first of the month, as monthly  data)

- Revenue

Table "Cost":

$Syn 1:

       - SKU ID

        - Date (always a first of the month, as monthly  data)

- Cost ID

- Cost

Table "SKU Info"

- SKU ID

- Brand

- Product Category

Table "Cost Info"

- Cost ID

- Cost Category

- Cost Sub-Category

- Cost Detail-Category  

I have defined the following measures:

"Cost": Sum(Cost)

"Revenues":  Sum({<[Cost ID]=,[Cost Category]=,[Cost Sub-Category]=,[[Cost Detail-Category]=>} Revenue)

"Cost Ratio": Sum(Cost)/Sum({<[Cost ID]=,[Cost Category]=,[Cost Sub-Category]=,[[Cost Detail-Category]=>} Revenue)

I run into the following issue: I have a bar chart where a user can drill down on the different cost categories down to sub-category level for the Cost Ratio by Month-Year.  If a user e.g. drills down to sub-category "Rework", he should see on a per month level which percentage of revenue has been spent for reworks for his current product selection (not just for those SKUs that happened to have rework done).

However, it seems like the bar chart insists to only show the percentage relative to the sum of revenues for SKUs that had some rework cost in the given month,  even though to my understanding the formula tells Qlik Sense not to filter the revenues depending on the cost when summing them up.

I have KPI visualizations above the chart that show me Cost, Revenues, and Cost Ratio. They seem to show the correct value. It is a bit frustrating because I can limit to a single month, and the single bar in the bar chart shows me a different value for the same measure than the KPI visualisations above (which appear to work correctly). I have a pie chart as well, which shows same "wrong" number as the bar chart... What am I doing wrong? Have I misunderstood something  horribly..? 

[Mako]

Hello,

I have tried around for another 5 days but I cannot seem to make it... Is the explanation not clear enough for you, or should I give any additional information?

I am getting a bit desperate, we are about to buy licenses for everyone and I cannot get the core KPI to work.

Thanks a lot in advance for any help!

[sunny_talwar]

Would you be able to share a sample app to make it easier for us to see the issue?

[Mako]

Hi,

I built a smaller app that shows the issue.

Just filter the report for e.g., 2019; March; and Rework.

The bar chart will show a different (wrong) percentage; the KPI chart above shows the correct one.

There seems to be some filtering of the revenues the KPI divides by going on inside the chart, even though the formula tells it not to.

Any help would be very much appreciated - our IT was not able to help out so far, and we won't be able to buy local licenses unless the use case is proven...

Thanks a lot!

[Vegar]

The issue lies within your denominator.

Try this formula:

Sum(Cost)/Sum(TOTAL{<[Cost Category]=,[Cost ID]=>} Revenue)

[Mako]

Thank you very much!

The issue I have when doing it this way, is that the ratio in the bar chart should be on a monthly basis.

If I add in the TOTAL, it works as long as only one month is selected. If I show two months, he will not show the ratio per month but the ratio over all sales over both months.

As in the attached screenshot, the total over February and March is 5.5% and he shows February 1.2% and March 3.5%.  What I would want is it to show 2.8% for February and 7.8% for March in the bar chart (the values you see if you filter for February only/March only respectively), which averages to 5.5% over February and March weighted by revenue... Is there a way he would still respect all filters the bar chart throws at him, and just completely disregards any explicit or implicit filtering based on which product had which kind of cost?

[Vegar · Accepted solution]

You can choose dimensions which should be ignored by the TOTAL command.
Sum(Cost)/Sum(TOTAL <Year, Month,MonthYear,Date>{<[Cost Category]=,[Cost ID]=>} Revenue)

[Mako]

Thanks a lot, now it works!!!