Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis - including one fact only if another fact occurs in same dimension

I have market sales data Market.Sales and our business Sales, Our.Sales.

I want to create a table, broken down by month that compares the 2, however I don't want to include any of our sales if we haven't yet got the market sales for that month.

I've got a straight table with the months as a dimension and am doing Sum expressions for each of the facts, but I'm not sure how to filter Our.Sales according to Market.Sales (within the month).

What expression should I put in my column to display Our.Sales such that they are only included if we have the corresponding month's Market.Sales?

1 Solution

Accepted Solutions
stigchel
Partner - Master
Partner - Master

Maybe something like this:

Sum({<Month=P({1<Market.Sales={">0"}>} Month)>} Our.Sales)

View solution in original post

2 Replies
stigchel
Partner - Master
Partner - Master

Maybe something like this:

Sum({<Month=P({1<Market.Sales={">0"}>} Month)>} Our.Sales)

sunny_talwar

Another way would be something like this

Sum({<Month = {"=Sum(Market.Sales) > 0"}>}Our.Sales)

or

Sum(Aggr(If(Sum(Market.Sales) > 0, Sum(Our.Sales)), Month))