Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fheidenstecker
Creator II
Creator II

Value of preceding quarter

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.

prec quarter.png

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

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
blade001
Contributor II
Contributor II

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

marcus_sommer

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

blade001
Contributor II
Contributor II

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

fheidenstecker
Creator II
Creator II
Author

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