Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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))
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.
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))
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?
I have attached the app.
Could you please show me how it can be solved?
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))
Thank so much! It works 🙂
Would you be able to share an updated sample to show what you have? and from the sample explain what you need to get?
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.
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.
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