Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Showing previous months sales - Set Analysis.

Hi there,


Trying to get my previous sales from last month subtracted from the current sales this month, so it shows the amount of sales (less or more) than the previous.


My current set analysis:

Count({<Month={">=$(=MonthStart(AddMonths(Max(Month),-1))) $(=MonthStart(AddMonths(Max(Month),0)))"}>}Sales_total)


This giving the wrong result, obviously. Not too sure how to structure my analysis. Any assistance would help.


Thanks.

20 Replies
MVP
MVP

Re: Showing previous months sales - Set Analysis.

Where are you trying to do this? In a text box object or a chart? If in a chart is your dimension Month (or some date time related dimension?)

Not applicable

Re: Showing previous months sales - Set Analysis.

Hi,

Trying to do this in a KPI.

The date (month) is a dimension.

jayaseelan
Contributor III

Re: Showing previous months sales - Set Analysis.

Are you trying to count the Sales_Total?

galax_allu
Valued Contributor

Re: Showing previous months sales - Set Analysis.

Hi

try this

Count({<Month={">=$(=MonthStart(AddMonths(Max(Month),-1))) $(=MonthEnd(AddMonths(Max(Month),0)))"}>}Sales_total)



or


count({<Month={">=$('>=' & MonthStart(AddMonths(Max(Month),-1)))&'<='& MonthEnd(AddMonths(Max(Month),0))"} Sales_total)

Not applicable

Re: Showing previous months sales - Set Analysis.

if the data type of Month field is dual  type (string and integer) use below expression

sum({<Month = { $(=month( MakeDate(1999, max(Month)  ,1) ) ) }>} Sales_total) - sum({<Month = { $(=month( MakeDate(1999,( max(Month) - 1 ) ,1) ) ) }>} Sales_total)

and if Month field is already an integer, you can use below

sum({<Month = { $(=max(Month)) }>} Sales_total) - sum({<Month = { $(=max(Month)-1) }>} Sales_total)

regards,

MVP
MVP

Re: Showing previous months sales - Set Analysis.

What is the month's field format?? Try this:

Count({<Month={"$(='>=' & Date(MonthStart(Max(Month),-1)), 'DateFieldFormatHere') & '<' & Date(MonthStart(Max(Month),0), 'DateFieldFormatHere'))"}>} Sales_total)

Not applicable

Re: Showing previous months sales - Set Analysis.

Hi,


No I already have a COUNT of sales using the current expression:

COUNT({$<Sales_total={">0"}>}  Sales_Total)

The dimension drives the months when selected.

Trying to do it when Month (June) - (May) gives a remainder of what the difference is (+ OR -).

Thanks.

Not applicable

Re: Showing previous months sales - Set Analysis.

YYYY-MM

(2016/05)*

Employee
Employee

Re: Showing previous months sales - Set Analysis.

Count({<Month={"$(=MonthStart(AddMonths(Max(Month),-1)))Sales_total)  //This will give your a count of last month

-

Count({<Month={"$(=MonthStart(Max(Month))Sales_total)     //This will give you a count of this month

//Subtraction will give a variance result