Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Kunkulis
Creator
Creator

Show last 5 values in a chart

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

Labels (3)
2 Solutions

Accepted Solutions
sunny_talwar

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

View solution in original post

sunny_talwar

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

View solution in original post

8 Replies
sunny_talwar

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

Kunkulis
Creator
Creator
Author

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

sunny_talwar

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
Kunkulis
Creator
Creator
Author

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
sunny_talwar

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

Kunkulis
Creator
Creator
Author

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-012018-022018-032018-042018-052018-062018-072018-082018-092018-102018-112018-122019-01
2018Q12018Q12018Q12018Q22018Q22018Q22018Q32018Q32018Q32018Q42018Q42018Q42019Q1

 

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? 

sunny_talwar

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

Kunkulis
Creator
Creator
Author

Thank you for your help