Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
Creator II

Last 3 Quarters in Dimension

Hi,

I have a table with the following data

Dimensions: Product (String data), Quarter (Example format: Q3-17)

Measure: sum(value)

The Quarter field has data like this

Q3-16

Q4-16

Q1-17
Q2-17

Q3-17

My question is, in the chart by default i can see all the above quarters but i want to show only the last 3 quarters (means only Q1-17,Q2-17,Q3-17). I tried writing manually using pick(match()) & is working fine but i want it to be automatic.

Means, in future, if Q4-17 comes into the picture, it should show only Q2-17,Q3-17,Q4-17 etc.

So, how to achieve it to show only the last (latest) 3 quarters in the chart ?

Thanks:)

1 Solution

Accepted Solutions
Champion

Hi Mohan,

You can try this:

you have the actual quarter with this: ceil(month(today())/3)

so you should compare your quarter data with the actual quarter and compare your "year" part from the quarter field with the actual year.

and with this you can have exactly the last 3 quarters:

=if(mid(Quarter,2,1)<= ceil(month(today())/3) and mid(Quarter,2,1)> ceil(month(today())/3)-3 and mid(Quarter,4,2)= right(year(today()),2), Quarter)

Regards,

Youssef

5 Replies
Champion

Hi Mohan,

You can try this:

you have the actual quarter with this: ceil(month(today())/3)

so you should compare your quarter data with the actual quarter and compare your "year" part from the quarter field with the actual year.

and with this you can have exactly the last 3 quarters:

=if(mid(Quarter,2,1)<= ceil(month(today())/3) and mid(Quarter,2,1)> ceil(month(today())/3)-3 and mid(Quarter,4,2)= right(year(today()),2), Quarter)

Regards,

Youssef

Employee

Hello,

I suggest having a "quarter counter" in your data. Add this field in the script:

Year(Date) * 4 + [Quarter Number]  AS [Quarter Counter].

(where [Quarter Number] is your... quarter number i.e. 1 for Q1, 2 for Q2, etc.)

Then, in your set analysis : [Quarter Counter] = {'>=\$(=MAX([Quarter Counter] - 3))'}

Regards,

Brice

Creator II
Author

I want to tell you few things.

1. i'm getting this quarter field from the database itself and there is no date field

2. i want it as a dimension to show the latest 3 quarters - not as expression

Champion

So try the solution i gave you and tell me.

Employee

You can extract the Quarter Number from the database field.

For instance: right(subfield(Quarter, '-', 1), 1) as [Quarter Number].

Try the solution I suggested and tick the option to suppress 0 values. You could also use an aggr to compute a calculated dimension but it's too complex and heavy IMHO.

Community Browser