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

[#2]

   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

[whiteline]

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

[#4]

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

vedn=EditionMonth

[jagan]

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.

[whiteline]

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'}>}')

[#7]

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

[#8]

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

[whiteline]

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

[#10]

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

[jagan · Accepted solution]

Hi Anil,

PFA file.

Regards,

Jagan.