topic Re: Last 3 Quarters in Dimension in QlikView

Last 3 Quarters in Dimension

mohan2391 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Last 3 Quarters in Dimension

YoussefBelloum — Wed, 13 Sep 2017 12:30:33 GMT

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

Re: Last 3 Quarters in Dimension

Brice-SACCUCCI — Wed, 13 Sep 2017 12:34:00 GMT

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

Re: Last 3 Quarters in Dimension

mohan2391 — Wed, 13 Sep 2017 12:55:51 GMT

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

Re: Last 3 Quarters in Dimension

YoussefBelloum — Wed, 13 Sep 2017 13:03:33 GMT

So try the solution i gave you and tell me.

Re: Last 3 Quarters in Dimension

Brice-SACCUCCI — Wed, 13 Sep 2017 13:05:01 GMT

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.