Barchart to show Current vs Past Year in current date

[Kat92]

Hi,

I want to create a barchart in Qlik sense to show Current Months sales and Same month Past Year Sales next to each other.

I have created the two Master measures below and data seem to be correct.

Sales=sum({<Date=>} Actual)

Sales PY={<Date= >} (above(sum( {<Date= >} [Actual]),12,3))

However, the x-axis that contains the date shows also past years points with 0 in both Sales and Sales PY. 

How can I exclude the old dates from the chart? I have tried using limitation in Dimensions section but  it does not work properly.

Thanks in advance

[Oleg_Troyansky]

Hi Kat92,

There are a couple of ways to build it, some are easier than others.

- I think, the easiest would be to use 2 separate dimensions - Month and Year, and allow Qlik Sense to show you monthly totals for each year. Then, if you have more than 2 years of data, you can limit the Years in Set Analysis.

- If you wanted to show the last 12 months, and always show the most current year, compared to the previous year's data, then you'd need to implement something called the "As Of Date" table - this is a well documented data modeling technique that you can find in many blogs, including my own.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[Kat92]

Thank you for your input. 

I have also tried the option 1 but do not know if I am doing something wrong as it always returns 0 for both Current and Past Year

Current=Sum({<Month={">=$(vMonth)"},Year={"$(vCurrentYear)"}>} Actual)

PY=Sum({<Month={">=$(vMonth)"},Year={"$(vPreviousYear)"}>} Actual)

Also, I want the chart not to be filtered by Date filter in the report and show always the 2023 months.

Am I doing something wrong?

Thanks in advance

[Oleg_Troyansky]

Hi,

If you use Month and Year as dimensions, then there is no need in Set Analysis - Qlik will aggregated Actual values associated with each Year and Month. Just try something like this:

Dim1: Month

Dim2: Year

Measure: sum(Actual)

If you need to limit years to just this and prior year, then add this condition, just for years:

Sum({<Year={">=$(vPreviousYear)"}>} Actual)

This is still a single Measure with these two dimensions, not two separate measures.

See if it works for you.

Cheers,