Using set analysis for point in time reporting

[Anonymous]

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

[Anonymous]

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

[Gysbert_Wassenaar]

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.

[Anonymous]

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...

[Anonymous]

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.

[Gysbert_Wassenaar]

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

[miikkaqlick · Accepted solution]

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

[Anonymous]

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