Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Help with Set Analysis Syntax

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.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Help with Set Analysis Syntax

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 Replies
MVP
MVP

Help with Set Analysis Syntax

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.

Not applicable

Help with Set Analysis Syntax

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

MVP
MVP

Help with Set Analysis Syntax

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)

Not applicable

Help with Set Analysis Syntax

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

MVP
MVP

Help with Set Analysis Syntax

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.

Community Browser