Show the last year data when using the date range in Qliksense

[asr587]

Hello All,

There is a scenario where I need to show the previous year date/month values in a table in Qliksense when selecting the current year date.

Here is the screenshot for the better clarity

When I select the date in the date filter it has to show the current year revenue and last year revenue for those metrics when I select time period in the date range picker.

Please help with this scenario ,not sure how to create a variables for this case.

Thanks in advance.

.

[marksouzacosta]

Very nice question @asr587. Time intervals are always challenging.

I'll give you some code and some fundaments and you will have to adapt it to your data, hopefully with minor changes. Keep in mind that there are multiple different ways to solve this problem.

My approach will be to get four variables:

vMinDate - to get the min date available in your app
vMaxDate - to get the max date available in your app
vMinPreviousDate - based on vMinDate minus one year
vMaxPreviousDate - based on vMaxDate minus one year

This is one sample of the ranges, when I have two full years selected. This is a good point to evaluate if this is what you are looking for:

When I have one Year selected:

And when I have one Year Month selected:

You can select any date range that the variables will calculate the desired ranges. Following the variables expressions - note replace O_ORDERDATE with your Date field:

Now you just need the expressions in your chart:

For the available date ranges:

Sum({<O_ORDERDATE = {">=$(=vMinDate)<=$(=vMaxDate)"}>} O_TOTALPRICE)

For the previous period:

Sum({<ORDER_YEAR =, O_ORDERDATE = {">=$(=vMinPreviousDate)<=$(=vMaxPreviousDate)"}>} O_TOTALPRICE)

For the previous year, you may have to include other fields to be ignored by your measure. In my example I have just added ORDER_YEAR followed by a equal sign. Add more if you need following the example below:

Sum({<ORDER_YEAR =, MyDateFiel1 =, MyDateFiel2 =, MyDateFielN =, O_ORDERDATE = {">=$(=vMinPreviousDate)<=$(=vMaxPreviousDate)"}>} O_TOTALPRICE)

I hope that helps. Again, there are many ways to solve this problem depending on the details of your requirement and data. You will find a great chapter about that with a very elegant approach in the following book: QlikView 11 Developer's: Develop Business Intelligence Applications With Qlikview 11 https://a.co/d/0jfgg8yp