Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
leenlart
Creator
Creator

Show clients with 0 sales in month but sales >0 in previous month

Hello, 

I need to create a table showing the clients that have not ordered this month.  

This is in Qlikview April 2019 R3.  

The table will show the client then two measures : sales this month, sales previous month.

My sales this month measure is as follows : 

Sum( {$<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU])

My sales last month measure is this : 

Sum( {<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}, MOIS_ANNEE={'$(vMoisPrecedent)'}, MOIS= >} [TONNAGE VENDU])

 

The month field, MOIS, is the current month, and it is selected by default on opening.  The fiscal year, EXERCICEFIS, is also selected by default, and it is the current fiscal year.  The user can change month, fiscal year or eventually calendar year, ANNEE.  

I have a variable, vMoisPrecedent, that sets the Previous month-year, based on these three fields.

So my problem is showing only the clients with 0 sales in sales this month

I tried this as a calculated dimension :

=if(aggr(Sum( {$<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU]) = 0,[CLIENT COMPLET]),[CLIENT COMPLET])

But it is giving me a table with zero clients listed, and in my measure columns, the total of all sales for that month.  

leenlart_0-1675091115743.png

 

I would REALLY appreciate any help!  

Thanks!!

Labels (1)
1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

A couple of ideas for you:

- If you REALLY want to do it in a Calculated Dimension, you should tweak your syntax the following way:

AGGR(   IF ( "sales expression" <> 0, CLIENT), CLIENT)

Notice the AGGR outside of IF and not inside of IF.

Now, you still have a problem with the scope of data. Once you select the current Month by default, it automatically selects those clients that HAD SALES in the current month. Those clients that DON'T HAVE SALES in current month, become excluded by your selection. You may have to try using the setting "Show all data" on that dimension, or use some of the more advanced techniques that I described in this blog post

2. A simpler solution could be to use the field CLIENT as a simple dimension and to condition all the expressions by the fact that the first expression (Sales this month) = 0. Again, you will probably need to use something like "Show all data" on the dimension and also disable zero and null lines in the Presentation properties of the chart.

3. Another possible solution is to include this condition as an Advanced Search condition in Set Analysis for all expressions. That could get a bit more tangled though.

Let me invite you to my lecture on Set Analysis and AGGR() at the Masters Summit for Qlik, which will take place on March 1st. You will learn many powerful techniques of using Set Analysis, AGGR, Calculated Dimensions, and more.

Cheers,

View solution in original post

3 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

A couple of ideas for you:

- If you REALLY want to do it in a Calculated Dimension, you should tweak your syntax the following way:

AGGR(   IF ( "sales expression" <> 0, CLIENT), CLIENT)

Notice the AGGR outside of IF and not inside of IF.

Now, you still have a problem with the scope of data. Once you select the current Month by default, it automatically selects those clients that HAD SALES in the current month. Those clients that DON'T HAVE SALES in current month, become excluded by your selection. You may have to try using the setting "Show all data" on that dimension, or use some of the more advanced techniques that I described in this blog post

2. A simpler solution could be to use the field CLIENT as a simple dimension and to condition all the expressions by the fact that the first expression (Sales this month) = 0. Again, you will probably need to use something like "Show all data" on the dimension and also disable zero and null lines in the Presentation properties of the chart.

3. Another possible solution is to include this condition as an Advanced Search condition in Set Analysis for all expressions. That could get a bit more tangled though.

Let me invite you to my lecture on Set Analysis and AGGR() at the Masters Summit for Qlik, which will take place on March 1st. You will learn many powerful techniques of using Set Analysis, AGGR, Calculated Dimensions, and more.

Cheers,

leenlart
Creator
Creator
Author

Oleg!  You're awesome!  

I went with solution 1, and then I just had to check "Suppress when value is null" and I get the list of clients who purchased in the previous month but didn't in the selected month!  

So my corrected calculated dimension looks like this : 

=aggr(if(Sum( {$<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU]) = 0,[CLIENT COMPLET]),[CLIENT COMPLET])

Thanks for the link to your blog, which was very well explained also (and I'll be following...) .

And now I'm off to convince my employer that they need to pay for my inscription for the masters summit!

Thanks very much for your help!  

Kathleen

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Kathleen,

My pleasure! Your feedback made my day, thank you! See you in my class!

Oleg