Last 3 Quarters in Dimension

[mohan2391]

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

[YoussefBelloum · Accepted solution]

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]

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]

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]

So try the solution i gave you and tell me.

[Brice-SACCUCCI]

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.