topic Re: Show last 5 values in a chart in QlikView

Show last 5 values in a chart

Kunkulis — Sat, 16 Nov 2024 04:18:35 GMT

Hello,

I to create a bar chart where on X-Axis I have quarter date written as YYYYQN (i.e. 2019Q1)
Using the Dimension Limits to show first 5 and reversing the order via Sort table give the information I want to see, but it show it in wrong order - current quarter is show on the left, but should be on the right.

The last option is to get it via Expressions, currently this returns the correct data, but it has no limitation on the last 5 quarters:

SUM({<Event={'Criteria1', 'Criteria2', 'Criteria3', 'Criteria4'}, CCY= %Currency>}
ExRate * [AMOUNT])/1000000

So I get chart to show information from 2006Q1

The Quarter column name is 'YearQuarter'

EDIT:
The items in YearQuarter are ordered in ascending order - starts with the oldest 2006Q1 and ends with 2019Q1

Re: Show last 5 values in a chart

sunny_talwar — Thu, 28 Feb 2019 13:38:52 GMT

Do you have a date field (in addtion to YearQuarter) to restrict for the last 5 quarter?

Re: Show last 5 values in a chart

Kunkulis — Thu, 28 Feb 2019 14:19:30 GMT

In the table where we are linked to dates, we do have several of them.

%EffectiveFromDate_Key
YearMonth
Date
WorkingDay
EndMonth
Month
Year
Week
Day
YearQuarter

Re: Show last 5 values in a chart

sunny_talwar — Thu, 28 Feb 2019 15:34:55 GMT

Then use Date field set analysis to do this

Sum({<Event = {'Criteria1', 'Criteria2', 'Criteria3', 'Criteria4'},
      CCY= %Currency,
      Date = {"$(='>=' & Date(QuarterStart(Max(Date), -5), 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>}
ExRate * [AMOUNT])/1000000

Re: Show last 5 values in a chart

Kunkulis — Thu, 28 Feb 2019 15:48:09 GMT

The solution does decrease the amount of Quarters I see, thank you.
I had a solution similar to this, but using endmonths and just having -13, but the issue with this one was, that the the sum would be wrong each month, since new values would be added to current quarter each month, but for the last quarter, each month they would reduce until it goes on to the next quarter. I hope you understood what I meant

Re: Show last 5 values in a chart

sunny_talwar — Thu, 28 Feb 2019 15:58:17 GMT

Nope, I don't really understand. Can you elaborate with an example may be?

Re: Show last 5 values in a chart

Kunkulis — Fri, 01 Mar 2019 05:54:17 GMT

This was the date range at the beginning I used:

{$<(EndMonth)={"$(='>='&AddMonths(MonthEnd(today((EndMonth))),-13)&'<='&MonthEnd(today()-1))"}

I used EndMonth because for all the new entries next to the registration date they show monthend date, so it felt a bit safer

2018-01	2018-02	2018-03	2018-04	2018-05	2018-06	2018-07	2018-08	2018-09	2018-10	2018-11	2018-12	2019-01
2018Q1	2018Q1	2018Q1	2018Q2	2018Q2	2018Q2	2018Q3	2018Q3	2018Q3	2018Q4	2018Q4	2018Q4	2019Q1

In the table above, you see the YYYY-MM and to which quarter it goes.

So my concern is that in the formula I used at first it would add now 2019-02 to 2019Q1, but it would remove the 2018-01 from 2018Q1, thus 2018Q1 would only have data for the 2018-02 and 2018-03.

If it is still unclear, maybe you can explain how your formula works?

Re: Show last 5 values in a chart

sunny_talwar — Fri, 01 Mar 2019 13:05:07 GMT

QuarterStart takes you to the beginning of the Quarter and that is why you should use QuarterStart instead of MonthStart or AddMonths. Give the expression I gave a shot and see what you get.

Also, you can test this expression in a text box object

='>=' & Date(QuarterStart(Today(), -5)) & '<=' & Date(Today())

to see what range will be used based on Today()

Re: Show last 5 values in a chart

Kunkulis — Fri, 01 Mar 2019 13:08:50 GMT

Thank you for your help