Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Compare this sum of sales in this month with the same month last year

I would like to compare the SALES_VALUE where the End_Date is selected from a list box (e.g. 31-12-2013) with the same month last year (e.g. 31-12-2012).  I tried using the following set analysis and it doesn't work.  Appreciate your time and effort to advise on how to make this work.

sum({$<END_DATE={$(=YEAR(END_DATE)-1 & '-' & =MONTH(END_DATE) & '-' & =DAY(END_DATE))}>} SALES_VALUE)

Tags (1)
2 Replies
hschultz
Contributor

Re: Compare this sum of sales in this month with the same month last year

Please try this for Year to date

Sum({$<D.Year={'$(vLastYear)'},D.Month={"=$(vMaxMonth)"},D.YearMonth=>}SALES_VALUE)

or this for single Month vs same Month previous year

Sum({$<D.Year={'$(vLastYear)'},D.Month={$(=max(D.Month))},D.YearMonth=>}SALES_VALUE)

Where

vLastYear = Max(D.Year) - 1

vMaxMonth = right(Max(D.YearMonth),2)

D.YearMonth excluded as it is the selection

MVP & Luminary
MVP & Luminary

Re: Compare this sum of sales in this month with the same month last year

Hi,

Try this expressions

For selected month:

Sum({$<YearDimensionName=, MonthDimensionName= QuarterDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName)))<=$(=MonthEnd(Max(DateDimensionName)))'}>}SALES_VALUE)

For previous year same month:

Sum({$<YearDimensionName=, MonthDimensionName= QuarterDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -12))<=$(=MonthEnd(Max(DateDimensionName), -12))'}>}SALES_VALUE)

Replace YearDimensionName=, MonthDimensionName= QuarterDimensionName=,DateDimensionName  with your corresponding date dimensions.

Hope this helps you.

Regards,

Jagan.