Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Denis2205
New Contributor II

Using Variable depending on date

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.

 

1.jpg

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.

2.jpg

These are my variables:

 

3.jpg  

4.jpg

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

 

4 Solutions

Accepted Solutions

Re: Using Variable depending on date

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

View solution in original post

Re: Using Variable depending on date

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

View solution in original post

Re: Using Variable depending on date

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.

View solution in original post

Re: Using Variable depending on date

Actually even with derived dates seems to be working for me

image.png

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

View solution in original post

16 Replies

Re: Using Variable depending on date

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
New Contributor II

Re: Using Variable depending on date

I have attached the app.

Could you please show me how it can be solved?

Re: Using Variable depending on date

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

View solution in original post

Denis2205
New Contributor II

Re: Using Variable depending on date

Thank so much! It works 🙂

Denis2205
New Contributor II

Re: Using Variable depending on date

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

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
New Contributor II

Re: Using Variable depending on date

Attached.

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

5.jpg

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

6.jpg

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

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
New Contributor II

Re: Using Variable depending on date

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