Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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......
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
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'}.
vx=If(EditionMonth=1, '{$<ModelYear={$(#=Only(ModelYear)-1)}, EditionMonth={"11"}>}','{$<EditionMonth={"$(=$(vEdn)-2)"}>}')
vedn=EditionMonth
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.
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'}>}')
Still its not working.....any other way to do this
Attached sample app to my orginal post.....please check it
Why did you say that your group has name 'GROUP1' (and use it in set expressions) when it has name 'Group' ?
its just sample file so i changed it into Group in orginal file it is GROUP1 only 🙂
Hi Anil,
PFA file.
Regards,
Jagan.