Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mohan2391
Creator II
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
YoussefBelloum
Champion
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

View solution in original post

5 Replies
YoussefBelloum
Champion
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

Brice-SACCUCCI
Employee
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

mohan2391
Creator II
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

YoussefBelloum
Champion
Champion

So try the solution i gave you and tell me.

Brice-SACCUCCI
Employee
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.