topic Re: Set analysis with if condition in QlikView

Set analysis with if condition

Thu, 06 Sep 2012 11:27:57 GMT

Hi All,

I have a below set expression this is Calculates variance of max edition - previous edtion for a year

$(vpreviousVariance)= pick(($(vSlider))/12,avg({<MaxEdition={1}>}$(v12m)) -

avg({<PreviousEdition={1}>}$(v12m)),avg({<MaxEdition={1}>}$(v24m)) -

avg({<PreviousEdition={1}>}$(v24m)),avg({<MaxEdition={1}>}$(v36m)) -

avg({<PreviousEdition={1}>}$(v36m)),avg({<MaxEdition={1}>}$(v48m)) -

avg({<PreviousEdition={1}>}$(v48m)),avg({<MaxEdition={1}>}$(v60m)) -

avg({<PreviousEdition={1}>}$(v60m)))

I have different groups(ABC,DEF,XYZ...etc) now i have to extract the variance for each groupfor this am trying to write the if condition for each group like $(vpreviousVariance)= pick(($(vSlider))/12,if(GROUP1='ABC', (avg({<MaxEdition={1}>}$(v12m)) -avg({<PreviousEdition={1}>}$(v12m))),.....rest of the expression.

But its giving wrong result. So how can we combine if condition with set analysis here......

Re: Set analysis with if condition

jagan — Thu, 06 Sep 2012 17:17:52 GMT

Hi Anil,

Try like this

$(vpreviousVariance)= pick(($(vSlider))/12,avg({<GROUP1={'ABC'}, MaxEdition={1}>}$(v12m)) -

avg({<GROUP1={'ABC'}, PreviousEdition={1}>}$(v12m)),avg({<GROUP1={'ABC'}, MaxEdition={1}>}$(v24m)) -

avg({<GROUP1={'ABC'}, PreviousEdition={1}>}$(v24m)),avg({<GROUP1={'ABC'}, MaxEdition={1}>}$(v36m)) -

avg({<GROUP1={'ABC'}, PreviousEdition={1}>}$(v36m)),avg({<GROUP1={'ABC'}, MaxEdition={1}>}$(v48m)) -

avg({<GROUP1={'ABC'}, PreviousEdition={1}>}$(v48m)),avg({<GROUP1={'ABC'}, MaxEdition={1}>}$(v60m)) -

avg({<GROUP1={'ABC'}, PreviousEdition={1}>}$(v60m)))

Hope this helps you.

Regards,

Jagan.

Re: Set analysis with if condition

Tue, 11 Sep 2012 05:05:08 GMT

Hi Jagan, Above expression is not working.. and below is the modified expression, here we have to add if condition. please have a look it

=round(pick(($(vSlider))/12,avg({$<EditionMonth = {$(#=Only(EditionMonth))}>}$(v12m)) - avg( $(vX)$(v12m) ),
avg({$<EditionMonth = {$(#=Only(EditionMonth))}>}$(v24m)) - avg( $(vX) $(v24m) ),
avg({$<EditionMonth = {$(#=Only(EditionMonth))}>}$(v36m)) - avg( $(vX) $(v36m) ),
avg({$<EditionMonth = {$(#=Only(EditionMonth))}>}$(v48m)) - avg( $(vX) $(v48m) ),
avg({$<EditionMonth = {$(#=Only(EditionMonth))}>}$(v60m)) - avg( $(vX) $(v60m) )))

Anil

Re: Set analysis with if condition

jagan — Tue, 11 Sep 2012 09:35:16 GMT

Hi,

Check this

=round(pick(($(vSlider))/12,avg({$<GROUP1={'ABC'}, EditionMonth = {$(#=Only(EditionMonth))}>}$(v12m)) - avg( $(vX)$(v12m) ),
avg({$<GROUP1={'ABC'}, EditionMonth = {$(#=Only(EditionMonth))}>}$(v24m)) - avg( $(vX) $(v24m) ),
avg({$<GROUP1={'ABC'}, EditionMonth = {$(#=Only(EditionMonth))}>}$(v36m)) - avg( $(vX) $(v36m) ),
avg({$<GROUP1={'ABC'}, EditionMonth = {$(#=Only(EditionMonth))}>}$(v48m)) - avg( $(vX) $(v48m) ),
avg({$<GROUP1={'ABC'}, EditionMonth = {$(#=Only(EditionMonth))}>}$(v60m)) - avg( $(vX) $(v60m) )))

Regards,

Jagan.

Re: Set analysis with if condition

Tue, 11 Sep 2012 09:50:41 GMT

Jagan i have tried yours exp but its giving same result as my expression..its not filtering.

Re: Set analysis with if condition

whiteline — Tue, 11 Sep 2012 09:58:39 GMT

Have you tried to check the parts of your expression that you use with pick function? Are they right ?

Or the problem is with picking the right expression.

If so, first get to work this: =pick(($(vSlider))/12, 1, 2, 3, 4, 5)

Re: Set analysis with if condition

Tue, 11 Sep 2012 10:49:21 GMT

Yeah i have checked the pick function its correct...problm is data is not filetring based on groups..

Re: Set analysis with if condition

whiteline — Tue, 11 Sep 2012 11:32:04 GMT

to extract the variance for each group

Could you explain a bit more what you meant ?

What is GROUP1 and how it's connected with your data.

Re: Set analysis with if condition

Tue, 11 Sep 2012 12:44:02 GMT

This expression is calculating the variance of all types of vehicle and here we are taking last 5 year sales values for this i have created 5 variables i.e.V12m,V24m..V60m.

And the above expression is calculating selected month-Previous month.

What i want is as i said we have different types of vehicles, now i want the variance for particular vehicle type, so i want like if condition to filter the above expression for particular vehicle type. GROUP1 dimenison have the vehicle types.

Re: Set analysis with if condition

whiteline — Tue, 11 Sep 2012 13:03:27 GMT

Ok.

As i see, in other words, you calculate the difference between one avarage and another average for a specified period of time.

And now you want to calculate it without making a selection in GROUP1 field (as user) just using set analysis.

Does It gives the right result if you just select one value from GROUP1 ?

What values have variables vX and v12m ?

Re: Set analysis with if condition

Tue, 11 Sep 2012 14:12:21 GMT

Yes correct, and it will give correct result if we select one value from GROUP1

v12m: just storing 12 months total sales(it is direct filed from database).

VX: in this if we select January chart should calculate January- previous year Decemeber month this formula we are storing here

Re: Set analysis with if condition

whiteline — Tue, 11 Sep 2012 14:40:42 GMT

As an example let's take the first expression:

=avg({$<EditionMonth = {$(#=Only(EditionMonth))}>}$(v12m)) - avg( $(vX)$(v12m) )

To select GROUP1 you should modfy set expressions.

for the first avg its trivial (add GROUP1={'ABC'} as mentioned above).

for the second avg you have to modify vX set expression and also add GROUP1={'ABC'}.

Re: Set analysis with if condition

Wed, 12 Sep 2012 07:19:11 GMT

vx=If(EditionMonth=1, '{$<ModelYear={$(#=Only(ModelYear)-1)}, EditionMonth={"11"}>}','{$<EditionMonth={"$(=$(vEdn)-2)"}>}')

vedn=EditionMonth

Re: Set analysis with if condition

jagan — Wed, 12 Sep 2012 07:35:24 GMT

Hi,

Try this

vx=If(EditionMonth=1, '{$<

GROUP1={'ABC'},

ModelYear={$(#=Only(ModelYear)-1)}, EditionMonth={"11"}>}','{$< GROUP1={'ABC'}, EditionMonth={"$(=$(vEdn)-2)"}>}')

Hope this helps you.

Regards,

Jagan.

Re: Set analysis with if condition

whiteline — Wed, 12 Sep 2012 07:39:03 GMT

As I mentioned above, to calculate the second avg right, you should also add condition GROUP1={'ABC'} in your expression for vx:

vx=If(EditionMonth=1, '{$<ModelYear={$(#=Only(ModelYear)-1)}, EditionMonth={"11"}, GROUP1={'ABC'}>}','{$<EditionMonth={"$(=$(vEdn)-2)"}, GROUP1={'ABC'}>}')

Re: Set analysis with if condition

Thu, 13 Sep 2012 06:13:47 GMT

Still its not working.....any other way to do this

Re: Set analysis with if condition

Thu, 13 Sep 2012 09:06:49 GMT

Attached sample app to my orginal post.....please check it

Re: Set analysis with if condition

whiteline — Thu, 13 Sep 2012 09:34:46 GMT

Why did you say that your group has name 'GROUP1' (and use it in set expressions) when it has name 'Group' ?

Re: Set analysis with if condition

Thu, 13 Sep 2012 09:51:06 GMT

its just sample file so i changed it into Group in orginal file it is GROUP1 only 🙂

Re: Set analysis with if condition

jagan — Thu, 13 Sep 2012 12:39:17 GMT

Hi Anil,

PFA file.

Regards,

Jagan.