Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Comparing YearMonth to Previous Year.

Hi,

I've created a set analysis to display the current 'selected' YearMonth (Current dimension) this is a date format YYYY/MM within the current DB field:

The analysis to calculate where sales total > 0 = COUNT({$<SALES_TOTAL={">0"}>}  SALES_TOTAL)


This will show the selected 'YearMonth' and the sales total for that specific month with sales > 0.

The YearMonth looks like " 2016/07 " which is an existing field.

I need to create an expression where the sales total >0 (as shown above) with the YEARMONTH (currently selected) - 12 months?

So, I am having problems trying to compare in a KPI last years figure.

Many thanks.

Tags (1)
6 Replies
MVP
MVP

Re: Comparing YearMonth to Previous Year.

I am confused - there is no date selection in

COUNT({$<SALES_TOTAL={">0"}>}  SALES_TOTAL)

So it will include data for dates according to user selections. I think you may have copied the incorrect expression into your post.

galax_allu
Valued Contributor

Re: Comparing YearMonth to Previous Year.

Hi

Try this

Current month (last year)            


2015/07

       Count({$<[YearMonth]={"$(=Date(AddYears(Max(YearMonth), -1), 'YYYY/MM'))"}>} SALES_TOTAL)



Last Month Sales    


2016/06


  Count({$<[YearMonth]={"$(=Date(AddMonths(Max(YearMonth), -1), 'YYYY/MM'))"}>} SALES_TOTAL)





                  











Not applicable

Re: Comparing YearMonth to Previous Year.

Hi Sorry,


I didn't explain this too well. I already have a dimension which calculates the month today (YEARMONTH).

I need to add to the existing expression to include last years comparison.

Not applicable

Re: Comparing YearMonth to Previous Year.

This doesn't seem to work

galax_allu
Valued Contributor

Re: Comparing YearMonth to Previous Year.

Hi

try the following...

Selecting  dates between 12 months and present month .....

Count({$<[YearMonth]={">=$(=Date(AddYears(Max(YearMonth), -1), 'YYYY/MM')<=$(=Date(max(YearMonth),'YYYY/MM'))"},SALES_TOTAL={">0"}>} SALES_TOTAL)

MVP
MVP

Re: Comparing YearMonth to Previous Year.

How about this:

This YearMonth

Count({$<YearMonth = {"$(='>=' & Date(MonthStart(Max({<SALES_TOTAL={">0"}>} YearMonth)), 'YYYY/MM') & '<=' & Date(MonthEnd(Max({<SALES_TOTAL={">0"}>} YearMonth)),'YYYY/MM'))"}>} SALES_TOTAL)

LastYear's YearMonth

Count({$<YearMonth = {"$(='>=' & Date(MonthStart(Max({<SALES_TOTAL={">0"}>} YearMonth), -12), 'YYYY/MM') & '<=' & Date(MonthEnd(Max({<SALES_TOTAL={">0"}>} YearMonth), -12),'YYYY/MM'))"}>} SALES_TOTAL)