topic Re: Using set analysis for point in time reporting in QlikView

Using set analysis for point in time reporting

Anonymous — Thu, 26 Jun 2014 12:27:13 GMT

I've got a copy of QlikView 11 for Developers and I've made use of a formula that they suggest for calculating month on month performance. I've edited it slightly so that it reflects the dimensions that I have within my QVDs so it now shows:

=((Sum({$<PeriodID={"<=$(=Max(PeriodID-1))"},year=, quarter=,month=,period=>}customer_gross_sales)/

Sum({$<PeriodID={"<=$(=Max(PeriodID-2))"},year=,quarter=,month=,period=>}customer_gross_sales)))-1

This nearly works but it gives a percentage that is 100% out e.g. it shows 19510% growth for one product when it should be 19410%.

I assume that it is ignoring the -1 at the end of the formula.

Can anyone tell me why, or help me correct the formula?

Thanks in advance,

Peter

Re: Using set analysis for point in time reporting

Anonymous — Thu, 26 Jun 2014 12:36:10 GMT

Changing it to -2 works, but I don't understand why.

Re: Using set analysis for point in time reporting

Gysbert_Wassenaar — Thu, 26 Jun 2014 12:36:59 GMT

I'd use $(=Max(PeriodID)-1) so the expression also works if more than one period is selected. Otherwise I don't see anything really wrong. The -1 is certainly not ignored, but I have no idea why your expression calculates the value you see returned.

Re: Using set analysis for point in time reporting

Anonymous — Thu, 26 Jun 2014 12:49:50 GMT

Thanks for your reply. It's a mystery to me too! I downloaded the data into Excel just to do a validation and noticed the difference. I then started to wonder if I actually know how to calculate percentage growth! Using -2 in the formula does seem to work although I don't think it should...

Re: Using set analysis for point in time reporting

Anonymous — Thu, 26 Jun 2014 13:15:22 GMT

Interestingly when you break the formula up into its constituent parts something unexpected happens. In my development dataset I've got data for Jan, Feb and March.

This formula: Sum({$<PeriodID={"<=$(=Max(PeriodID-1))"},year=, quarter=,month=,period=>}customer_gross_sales)

Gives me the sum of January and February? I expected it to give me the sales for February.

Re: Using set analysis for point in time reporting

Gysbert_Wassenaar — Thu, 26 Jun 2014 13:17:21 GMT

<= means smaller or equal. If you want only equal then you can use PeriodID={"$(=Max(PeriodID)-1)"} instead.

Re: Using set analysis for point in time reporting

miikkaqlick — Thu, 26 Jun 2014 13:26:13 GMT

Yes, as I see it correct formula would be:

=(Sum({$<PeriodID={"=$(=Max(PeriodID)-1)"},year=, quarter=,month=,period=>}customer_gross_sales)/

Sum({$<PeriodID={"=$(=Max(PeriodID)-2)"},year=,quarter=,month=,period=>}customer_gross_sales))-1

This formula would compare max period with previous period.

Miikka

Re: Using set analysis for point in time reporting

Anonymous — Thu, 26 Jun 2014 13:49:14 GMT

Thanks Mikka, you're correct that's what it should be. I'd stupidly edited the formula for Ytd vs YTD last year and come up with the wrong formula.

I've now got what you suggested:

(Sum({$<PeriodID = {"$(=Max(PeriodID-1))"}, Year = , Quarter = ,

Month = , Period = > } customer_gross_sales)

Sum({$<PeriodID = {"$(=Max(PeriodID-2))"}, Year = , Quarter = ,

Month = , Period = > } customer_gross_sales))

- 1