Using Variable depending on date

[Denis2205]

Hello guys.

Met a problem in QlikSense. I want to show the current value of KPI and show it in the dynamics in the context of months. But the values in the table are calculated incorrect until you select one period. The values in the table vary depending on the selected period, but it should not be so. Help solve this problem.

For example, when i do not choose the period in filter it shows one value, when i choose period, it shows another value.

Now it shows 750 in 2019-aprl. And it is not correct value.

And when you sum values of vClosedAtTime it is not equal to 6546, but it have to be.

So, when i choose april 2019, it shows another valure, but correct.

These are my variables:

  

I think that variables vPeriodBegin and vPeriodEnd are not calculating correctly in table.

[sunny_talwar]

I think what you need here is Aggr() with if statement instead of using set analysis. Set analysis is evaluated once per chart and that is why you are seeing a different result based on selection and no selection. In order to help you better, would you be able to share a sample?

[Denis2205]

I have attached the app.

Could you please show me how it can be solved?

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(
If([Создано GMT+03:00] <= (max(MonthEnd(CanonicalDate)) + MakeTime(3)) and [Закрыто GMT+03:00] >= (min(MonthStart(CanonicalDate))+MakeTime(3)) and [Закрыто GMT+03:00] <= (max(MonthEnd(CanonicalDate)) + MakeTime(3)), [Подсчет])
, [Закрыто GMT+03:00], [Создано GMT+03:00], CanonicalDate.autoCalendar.YearMonth))

[Denis2205]

Thank so much! It works 🙂

[Denis2205]

Now i have another issue.
I need to show this values on the chart, but only last 12 months, ignoring the selected canonical date.
I used set analysis between the SUM and AGGR, but it work not correctly. It shows me last 12 months only, but when i select date, for example 2019, it shows me 2018 as 0.
How to make it?

[sunny_talwar]

Would you be able to share an updated sample to show what you have? and from the sample explain what you need to get?

[Denis2205]

Attached.

I want to show dynamics in last 12 months. Like this:

But when i select, for example 2018, it shows me values of only 2018.I need all values of last 12 months.

I need to ignore date selection. There will be more filters in this sheet, therefore it is important to ignore only the date (canonical date)  filter.

[sunny_talwar]

When 2018 is selected, you would want to still see the latest 12 months or 12 months from the max value? I can't read your month year, but if we go by calendar year, would you want to see 12 months from Jan-2018 to Dec-2018 when 2018 is selected and if the user select July 2018, you would want to see Aug-2017 to July-2018?

Also, it seems that the attached sample only has data for 2018 & 2019, is this true for your real app, or is this only a sample of the overall data? The reason I ask this is because you need to have older months  in your app if you want to display them in the chart.

[Denis2205]

I need to see 12 last months from today. 

I have only 2018, 2019 year data  

So here are translations:

Янв -January

Фев - February

Мар - March

Апр - Apr

Май - May

Июн - June

Июл - July

Авг -August

Сен - September

Окт- October

Ноя - November

Дек - December

I hope it helps