Help with Set Analysis Syntax

[#1]

Hi,

I'm trying to create a chart which represents a change in growth. The calculation would compare sales for this quarter compared to sales in the same quarter of the prior year. To support this function I created a QuarterIndex field. For example:

YearQtr QuarterIndex POSQty
2009-Q3 11 15,000
2008-Q3 7 10,000

The formula is: (2009-Q3 POSQty / 2008-Q3 POSQty) - 1

I tried this expression, but it doesn't work: (sum(POSQty) / sum( {$<QtrIndex= {"QtrIndex - 4"}>} POSQty )) - 1

Can someone help me with the correct syntax?

Thank you in advance for any help you can give. I'm open to any suggestions, since I've tried many variations over the past 3 days to no avail. I'm completely out of ideas.

[Miguel_Angel_Baeyens]

You can compare quarters and substracting one year to that with something like

(sum(POSQty) / sum( {$<YearQtr = {$(#=QuarterName(Date(timestamp#(YearQtr,'YYYY-MMM'))-4))}>} POSQty )) - 1

For that I'd recommend you to use some different timestamp for your YearQtr field, to same something like '2009 abr-jun' in spite of how are you going to display your quarters in the chart.

[#3]

Thanks, but we're using 16-week quarters, so it is not as easy as subtracting months.

[Miguel_Angel_Baeyens · Accepted solution]

Then you need to take into account only the quarter index:

Sum({$< QuarterIndex = {"11"} >} POSQty) / Sum({$< QuarterIndex = {$(#QuarterIndex)-4} >} POSQty) - 1

Use a variable to store current QuarterIndex so you can change {"11"} in the expression above for {$(#vCurrentQuarterIndex)} (i. e)

[#5]

Thank you Miguel! Out of curiousity, why the hash mark? I haven't seen this before $(#QuarterIndex)???

[Miguel_Angel_Baeyens]

As QlikView doesn't work with data types, it's very useful to say QlikView which kind of information you are working with. The hash mark means "numeric". $(#QuarterIndex) means numeric value of QuarterIndex, as you are going to do some maths with it.