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 · Accepted solution]

So, regardless of the selection, you always want to see 12 months from today? May be try this

Sum({1<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}
Aggr(
If(Only({1<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}[Создано GMT+03:00]) <= (Max({1<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}MonthEnd(CanonicalDate)) + MakeTime(2,59,59)) 
and Only({1<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}[Закрыто GMT+03:00]) >= (Min({1<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}MonthStart(CanonicalDate))+MakeTime(3)) 
and Only({1<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}[Закрыто GMT+03:00]) <= (Max({1<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}MonthEnd(CanonicalDate)) + MakeTime(2,59,59))
,
Only({1<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}[Подсчет]))
, [Закрыто GMT+03:00], [Создано GMT+03:00], CanonicalDate.autoCalendar.YearMonth))

[Denis2205]

It works, BUT:

1. It works very slow, because of this big formula.

In my real app there are 9 charts like this, so i think Qlik will die there. May be there is another way?

2. It ignores all the filters. In attached case i have a simple sample. But in a real app i have more filters and interactive charts, so i need to show this chart just ignoring the CanonicalDate field.

[sunny_talwar]

1) You can look into implementing The As Of Table

2) You can remove 1 from your set analysis and it will only ignore the fields specified in your set analysis

{1<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}

 So, remove red 1, and keep green field names

[Denis2205]

1. I will read it, ok.

2. When i remove  1 from set analysis, it does not work. When i select 2019 in filter, it shows me just months of 2019 year.

[sunny_talwar · Accepted solution]

1) I think this might be because you are using derived calendar. May be create a regular calendar object and then ignoring selection should work.

[sunny_talwar · Accepted solution]

Actually even with derived dates seems to be working for me

In the image above, I selected 2018, but still see 2019 in the chart and this was my expression

Sum({<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}
Aggr(
If(Only({<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}[Создано GMT+03:00]) <= (Max({<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}MonthEnd(CanonicalDate)) + MakeTime(2,59,59)) 
and Only({<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}[Закрыто GMT+03:00]) >= (Min({<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}MonthStart(CanonicalDate))+MakeTime(3)) 
and Only({<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}[Закрыто GMT+03:00]) <= (Max({<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}MonthEnd(CanonicalDate)) + MakeTime(2,59,59))
,
Only({<[CanonicalDate.autoCalendar.Date], [CanonicalDate.autoCalendar.Month], [CanonicalDate.autoCalendar.Quarter], [CanonicalDate.autoCalendar.Year]>}[Подсчет]))
, [Закрыто GMT+03:00], [Создано GMT+03:00], CanonicalDate.autoCalendar.YearMonth))

[Denis2205]

i read about AS OF TABLE, and do not know how it can helps me.

But i created regular calendar, and it is ok. thanks.

Now i can not understand, why date range does not work. When i want to select a period within the month, for example from 13.04.2019 to 30.04.2019 it does not react.