topic Re: Prior Period Sum (Set Analysis) in QlikView

Prior Period Sum (Set Analysis)

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

Hi All,

Trying to do what I thought would be pretty straight forward but having no luck.

Hopefully putting this in front of some fresh eyes will get me where I want to be.

I have FullDate as my dimension and then two expressions. First expression is a simple Sum(MetricValue) and the second is the same sum but for seven days prior. Something like this: Sum({<FullDate= {'$(=Date(FullDate-7),'MM/DD/YYYY'))'}>}MetricValue)

Unfortunately this is not working.

Trying to avoid using If statement as the data is extremely high volumes. I cannot create it in the script as the number of days could vary. Below is a table of expected results.

Thanks so much for any creative ideas that someone my have.

FullDate

Sum

Sum Prior 7

10/19/2018

10/18/2018

10/17/2018

114

10/16/2018

101

10/15/2018

10/14/2018

10/13/2018

10/12/2018

10/11/2018

10/10/2018

10/09/2018

10/08/2018

10/07/2018

10/06/2018

Re: Prior Period Sum (Set Analysis)

shreya_nadkarni — Tue, 23 Oct 2018 07:24:22 GMT

Hi,

You could try this

1st Exp:= sum(sum)

2nd Exp:= Below(Sum(sum),7)

Re: Prior Period Sum (Set Analysis)

nelson_sm — Tue, 23 Oct 2018 14:33:46 GMT

Excellent suggestion although I should have provide more detail. I would only want to see the first seven lines. When using dimension limits it's unable to reference. I would be ideal to somehow utilizing set analysis.

Re: Prior Period Sum (Set Analysis)

shreya_nadkarni — Fri, 26 Oct 2018 09:07:27 GMT

Hi Steve,

For the above requirement you could Hide the second column in the presentation.

Also limit the max Number to 7 in the presentation for the 3rd column.

Hope this is helpful.Thanks