topic Value of preceding quarter in QlikView

Value of preceding quarter

fheidenstecker — Wed, 13 Jan 2016 11:24:28 GMT

Hello Qlik Community,

I have a rather tricky requirement, and I am Stuck at the moment 😞

I have a table with values for each month. I want to have the end value of the preceding quarter for eacht month.

Meaning for July, August and September 2015, I want to have the value for June 2016.

I tried using the above Function, but no luck...

Does Anyone have a clue? Can this be solved by an expression, or maybe it is necessry to implement this in a script?

I would really appriciate some suggestions 😉

For a better understanding, I attached a sample file. In real life, I also need data for the prepreceding and the preprepreciding Quarter, but i guess once you have figured it out for 1 quarter, you can adapt the solution. Hopefully .

Cheers from Germany!

Fabian

Re: Value of preceding quarter

blade001 — Wed, 13 Jan 2016 11:47:14 GMT

Hi FHeidenstecker

At a quick glance this can be resolved at back-end in the scripting. Load the table and use Peek() and Prev() to derive the value for "End Value Of Last Quarter". I can send you an example qvw once I get back to my desk. Hope this helps.

-S.K.Rab

Re: Value of preceding quarter

marcus_sommer — Wed, 13 Jan 2016 12:45:31 GMT

Hi,

the above() needs a bit "intelligence". In your (simple) case would this work.

=Above(Sum(Value),pick(match(mod(rowno(), 3), 0,1,2), 3,1,2))

The values from last quarter 2013 are missing in this case - here could be a wrapping with an aggr-function work - see this as hint: Re: RangeSum with Aggr. Alternativ to the above solution (which only worked if there are no period-selections) could be applied a month-offset within the master-calendar.

- Marcus

Re: Value of preceding quarter

blade001 — Wed, 13 Jan 2016 13:20:05 GMT

Hi,

Please see the attached. This demonstrates how you can do this in scripting using the methods I have mentioned earlier and also the fix from Marcus (posted above) if you want to do this in the chart expression. You will face some difficulties with selections if you use the expression. however using the script solution would not give you that problem.

I have used the following to derive the End_Value_of_Last_Quarter. you can derive this column during your load stage.

If(Quarter <> Previous(Quarter),Previous(Value),Peek(End_Value_of_Last_Quarter)) AS End_Value_of_Last_Quarter

Hope this helps.

-S.K.Rab

Re: Value of preceding quarter

fheidenstecker — Sun, 31 Jan 2016 17:51:00 GMT

Thank you Marcus_Sommer‌ for your help!

I guess I'll never understand this above voodoo 😉

I Managed to match my requirement by using some Aggr and Set Analysis!

-Fabian