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......
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.
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
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.
Jagan i have tried yours exp but its giving same result as my expression..its not filtering.
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)
Yeah i have checked the pick function its correct...problm is data is not filetring based on groups..
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.
=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.
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 ?