21 Replies Latest reply: Sep 14, 2012 5:35 AM by jagan mohan rao appala

# Set analysis with if condition

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

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

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

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

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

• ###### Re: Set analysis with if condition

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

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

• ###### Re: Set analysis with if condition

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

=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) )))

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

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

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

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

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

vedn=EditionMonth

• ###### Re: Set analysis with if condition

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

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

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

• ###### Re: Set analysis with if condition

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

• ###### Re: Set analysis with if condition

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

its just sample file so i changed it into Group in orginal file it is GROUP1 only :-)

• ###### Re: Set analysis with if condition

Hi Anil,

PFA file.

Regards,

Jagan.

• ###### Re: Set analysis with if condition

What is problem ?

Correcl Vx:

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

Correct expression:

=round(pick((\$(vSlider))/12,avg({\$<Group={'ABC'}, EditionMonth = {\$(#=Only(EditionMonth))}>}\$(v12m)) - avg( \$(vX) \$(v12m) ),

avg({\$<GROUP={'ABC'},  EditionMonth = {\$(#=Only(EditionMonth))}>}\$(v24m)) - avg( \$(vX) \$(v24m) ),

avg({\$<GROUP={'ABC'},  EditionMonth = {\$(#=Only(EditionMonth))}>}\$(v36m)) - avg( \$(vX) \$(v36m) )))

• ###### Re: Set analysis with if condition

Hi Anil,