topic Help with Set Analysis Syntax in QlikView

Help with Set Analysis Syntax

Wed, 02 Dec 2009 14:28:22 GMT

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.

Help with Set Analysis Syntax

Miguel_Angel_Baeyens — Wed, 02 Dec 2009 14:45:06 GMT

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.

Help with Set Analysis Syntax

Wed, 02 Dec 2009 17:58:52 GMT

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

Help with Set Analysis Syntax

Miguel_Angel_Baeyens — Wed, 02 Dec 2009 18:22:16 GMT

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)

Help with Set Analysis Syntax

Wed, 02 Dec 2009 20:27:44 GMT

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

Help with Set Analysis Syntax

Miguel_Angel_Baeyens — Thu, 03 Dec 2009 14:10:21 GMT

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.