Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Mako
Contributor
Contributor

Set Expression issue for sum - different result in visualizations

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..? 

1 Solution

Accepted Solutions
Vegar
Partner
Partner

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)
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

7 Replies
Mako
Contributor
Contributor

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
Contributor
Contributor

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
Partner
Partner

The issue lies within your denominator.

Try this formula:

 

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Mako
Contributor
Contributor

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
Partner
Partner

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)
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Mako
Contributor
Contributor

Thanks a lot, now it works!!!