Set analysis with if condition

[#1]

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......

[jagan]

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.

[#3]

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

[jagan]

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.

[#5]

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

[whiteline]

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)

[#7]

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

[whiteline]

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.

[#9]

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

[whiteline]

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 ?