topic Re: Rolling Fiscal Quarter in QlikView

Rolling Fiscal Quarter

Anonymous — Fri, 08 Oct 2010 02:36:39 GMT

So I have to create sales for current quarter and preceding quarters using set analysis.

I've got a numeric QuarterID in my script and am using the following expression:

SUM({$<QuarterID = {$(=ONLY(QuarterID)-1)}>}Sales)

But this is only working if I specifically select numeric QuarterID from a list box. I would much rather the user select a year and Q1, Q2, etc. But this doesn't seem to work, even though the QuarterID seems correctly associated with the combination of year and Quarter. It also seems that if I select Year and the QuarterID, it still displays correctly. It is when I add in the Q1 the link stops working correctly. Something is wrong here...

Rolling Fiscal Quarter

Anonymous — Fri, 08 Oct 2010 02:56:06 GMT

And getting more to root of issue, the expression works as expected when no subtraction is involved. I can select Year and Quarter with no issue and it will display current quarter sales using set analysis, it is the addition of the "-1" that is causing the problem. I am missing something here...

This works:

SUM({$<QuarterID = {$(=ONLY(QuarterID))}>}Sales) //for displaying current quarter

This does not work with associated selections:

SUM({$<QuarterID = {$(=ONLY(QuarterID)-1)}>}Sales) //for displaying previous quarter

Rolling Fiscal Quarter

Anonymous — Fri, 08 Oct 2010 03:44:17 GMT

Nevermind... I got it. I just added a further code to ignore the "Q" selection. Worked like a charm...

Rolling Fiscal Quarter

Tue, 01 Feb 2011 11:21:34 GMT

What code you added to ignore Q in the Quarter?

Rolling Fiscal Quarter

suniljain — Tue, 01 Feb 2011 11:29:16 GMT

you can ignotre Q by Right Function

Rolling Fiscal Quarter

Tue, 01 Feb 2011 12:48:55 GMT

Got it!

Re: Rolling Fiscal Quarter

eleni_theodorid — Thu, 27 Oct 2011 21:00:53 GMT

Hi,

Could you please post the answer? Cause, I couldn't make it work right.

Do you mean something like this sum( {$<FiscalQuarter={$(=Right({<FiscalQuarter={$(=Only(FiscalQuarter))}>},1))}>} Sales) ???

Thanks in advance,

Helen