topic Re: Open sheet should select always latest 5 Quarters ? in QlikView

Open sheet should select always latest 5 Quarters ?

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

Hi,

When i Open a sheet always latest 5 Quarters should be selected in my Qlikview Report , how can we do it?

my Quarter field is FY_QYM

in that values is as per below

Best Regards,

Re: Open sheet should select always latest 5 Quarters ?

prieper — Thu, 25 Oct 2018 14:12:30 GMT

You will have to apply a numeric value (straight numbering or a date-value) to the string denominating the quarter.

Subject to this you will have to create a trigger on document open to select the topmost 5 values.

See a simple example in the attached file - think that you will catch the point.

Re: Open sheet should select always latest 5 Quarters ?

Anil_Babu_Samineni — Thu, 25 Oct 2018 14:21:57 GMT

Check if that helps

Re: Open sheet should select always latest 5 Quarters ?

dunnalahk — Thu, 25 Oct 2018 17:36:38 GMT

HI,

I tried but it's not working as expected. i am ttaching my app in original Request. can you try it there.

Re: Open sheet should select always latest 5 Quarters ?

dunnalahk — Thu, 25 Oct 2018 17:46:56 GMT

Re: Open sheet should select always latest 5 Quarters ?

Anil_Babu_Samineni — Fri, 26 Oct 2018 04:46:34 GMT

Before open yours, Have you attempted mine?

Re: Open sheet should select always latest 5 Quarters ?

dunnalahk — Fri, 26 Oct 2018 08:29:16 GMT

Hi Anil,

Yes i tried yours as well. but not able to.

one more point is when you do rank always it should take latest 5 quarters means Q4FY17, Q1FY18, Q2FY18, Q3FY18 and Q4FY18.

Re: Open sheet should select always latest 5 Quarters ?

prieper — Fri, 26 Oct 2018 10:00:58 GMT

I can't see your app?

Re: Open sheet should select always latest 5 Quarters ?

dunnalahk — Fri, 26 Oct 2018 10:25:24 GMT

HI Peter,

It says Default Test below. That's my app.

Re: Open sheet should select always latest 5 Quarters ?

dunnalahk — Mon, 29 Oct 2018 09:56:35 GMT

Hi Peter,

I have created a Rolling 5 Quarters variable in my attached app. but when i try to pass it to sheet open trigger it is not taking it.

Re: Open sheet should select always latest 5 Quarters ?

MK9885 — Mon, 29 Oct 2018 15:58:55 GMT

Not sure how you can achieve triggers but to get 5 Quarts combine try...

In back end

if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,

if(InQuarter(TempDate, today(),-2), 1, 0) as LastQtrFlag1,

if(InQuarter(TempDate, today(),-3), 1, 0) as LastQtrFlag2,

if(InQuarter(TempDate, today(),-4), 1, 0) as LastQtrFlag3,

if(InQuarter(TempDate, today(),-5), 1, 0) as LastQtrFlag4,

And in front end

if(GetSelectedCount(Year)=0,

aggr(Only({ < LastQtrFlag={1} >+< LastQtrFlag1={1}>+<LastQtrFlag2={1}>+<LastQtrFlag3={1} >+< LastQtrFlag4={1} >} [Quarter Year]) ,[Quarter Year]), [Quarter Year])

This would give you by default always last 5 quarters with no selections but not sure how to set this inside a trigger.

Maybe someone else has better answer or can create a flag from this etc...

Re: Open sheet should select always latest 5 Quarters ?

passionate — Mon, 29 Oct 2018 16:30:22 GMT

Hi,

PFA, Solution.

Regards,

Pankaj

Re: Open sheet should select always latest 5 Quarters ?

prieper — Tue, 30 Oct 2018 08:35:25 GMT

Dear,

a sample would have been enough - not the entire database ....

Anyhow:

Your row 65 does not make much sense, but you may rewrite it to

DUAL(FY_QM, RIGHT(FY_QM, 2) * 100 + MID(FY_QM, 2, 1) AS FY_QM_new

This would also convert the string into a (senseless, but) numeric expression, which then might be filtered.

For the on-open-event please then create a trigger

='=Rank(FY_QM_New)<=5'

HTH
Peter

Re: Open sheet should select always latest 5 Quarters ?

dunnalahk — Tue, 30 Oct 2018 12:27:56 GMT

Hi,

Thank you .

i wrote like below in script

AutoNumber(FYear & FY_QM, 'QuarterID') as [QuarterID]

and open sheet i have given as below in triggers

'=Rank(QuarterID)<=5'

It works. 🙂

Re: Open sheet should select always latest 5 Quarters ?

prieper — Tue, 30 Oct 2018 13:57:31 GMT

Hi,

you need to be careful, as AUTONUMBER will read the values in the order of entry of the , i.e. if 2018 will be loaded before 2017, it will get a lower number as if it would be loaded in the numeric sequence.

Give it a try with

Data:

LOAD *,

AUTONUMBER(Year & Quarter) AS id

INLINE

[Year, Quarter, Value

2018, Q118, 2

2018, Q218, 4

2017, Q118, 5];

and watch the outcome ...