topic Re: Using Variable depending on date in App Development

Using Variable depending on date

Denis2205 — Tue, 30 Apr 2019 12:21:24 GMT

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.

Re: Using Variable depending on date

sunny_talwar — Tue, 30 Apr 2019 11:21:53 GMT

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?

Re: Using Variable depending on date

Denis2205 — Tue, 30 Apr 2019 12:23:40 GMT

I have attached the app.

Could you please show me how it can be solved?

Re: Using Variable depending on date

sunny_talwar — Tue, 30 Apr 2019 13:01:56 GMT

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))

Re: Using Variable depending on date

Denis2205 — Tue, 30 Apr 2019 13:51:40 GMT

Thank so much! It works 🙂

Re: Using Variable depending on date

Denis2205 — Mon, 06 May 2019 09:15:04 GMT

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?

Re: Using Variable depending on date

sunny_talwar — Mon, 06 May 2019 11:57:15 GMT

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

Re: Using Variable depending on date

Denis2205 — Mon, 06 May 2019 12:34:32 GMT

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.

Re: Using Variable depending on date

sunny_talwar — Mon, 06 May 2019 12:50:32 GMT

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.

Re: Using Variable depending on date

Denis2205 — Mon, 06 May 2019 13:07:47 GMT

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

Re: Using Variable depending on date

sunny_talwar — Mon, 06 May 2019 13:22:46 GMT

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))

Re: Using Variable depending on date

Denis2205 — Mon, 06 May 2019 13:40:44 GMT

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.

Re: Using Variable depending on date

sunny_talwar — Mon, 06 May 2019 13:49:15 GMT

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

Re: Using Variable depending on date

Denis2205 — Mon, 06 May 2019 13:59:46 GMT

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.

Re: Using Variable depending on date

sunny_talwar — Mon, 06 May 2019 14:42:22 GMT

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.

Re: Using Variable depending on date

sunny_talwar — Mon, 06 May 2019 14:51:54 GMT

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))

Re: Using Variable depending on date

Denis2205 — Wed, 08 May 2019 20:50:04 GMT

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.