2 Replies Latest reply: Jun 2, 2014 1:07 PM by John McCarthy

# Set Analysis problem

This is my first post in the QLik Community so be nice

I have a problem where i need to show separate lines on a graph for seperate years.  The following works OK.

sum

({\$<Del.WeekYear={\$(=max(Del.WeekYear))}> } DeliveryQty)

and this also works

sum

({\$<Del.WeekYear={\$(=max(Del.WeekYear-1))}> } DeliveryQty)

However i now need to graph a number which I need to calculate as follows:

The number is calculated using the following formula which works.

sum

({<DisplayName={'Main Electricity'} >} Data )/((Sum({\$<DisplayName={'Weigh Belt'} > } Data)
)/1000
* (1-
Avg({\$<DisplayName={'Moisture'}, Data={">1"} > } Data)/100))

I now need to graph this in the same fashion as the first example but don't appear to be able to get the syntaz right to apply the set

({\$<Del.WeekYear={\$(=max(Del.WeekYear-1))}> }

i have tried:

sum

({\$<Del.WeekYear={\$(=max(Del.WeekYear))}> }{<DisplayName={'Main Electricity'} >} Data )/((Sum({\$<DisplayName={'Weigh Belt'} > } Data)
)/1000
* (1-
Avg({\$<DisplayName={'Moisture'}, Data={">1"} > } Data)/100))

and

sum

({\$<Del.WeekYear={\$(=max(Del.WeekYear-1))}> } {<DisplayName={'Main Electricity'} >} Data )/((Sum({\$<DisplayName={'Weigh Belt'} > } Data)
)/1000
* (1-
Avg({\$<DisplayName={'Moisture'}, Data={">1"} > } Data)/100))

Both of these formulas result in the same line being graphed even though :

{\$<Del.WeekYear={\$(=max(Del.WeekYear))}> } evaluates to 2014

{\$<Del.WeekYear={\$(=max(Del.WeekYear-1))}> } evaluates to 2013

John

• ###### Re: Set Analysis problem

set start with {< an ends with >} modifier fields can be seperated by commas

ex: sum ({<Year= {2010}, Customer = {'ABC'}>}Sales)

sum

({\$<Del.WeekYear={\$(=max(Del.WeekYear))}, DisplayName={'Main Electricity'} >} Data )/((Sum({\$<DisplayName={'Weigh Belt'} > } Data)
)/1000
* (1-
Avg({\$<DisplayName={'Moisture'}, Data={">1"} > } Data)/100))

• ###### Re: Set Analysis problem

Perfect Thanks.

Funny how the answer is always obvious when you see it

thanks again

John