Loops in Set Analysis

[Nitish91]

I am trying to calculate YOY % based on the selections(multiple) which user make.

Field used for Selection is Week_Desc (eg . below screen )

Set Expression for max two selections used is as below :

if(GetSelectedCount(Week_Desc)=2,((
sum({<Brand={'Total Market'},Week_Desc={$(=Chr(39)&GetFieldSelections(Week_Desc,Chr(39)&','&Chr(39))&Chr(39))}, Date=,MonthYear=,NumMonth=>}Volume))/

((sum({<Brand={'Total Market'},Week_Desc=, AT_Year={"$(=left(right(SubField(Concat(DISTINCT GetFieldSelections(Week_Desc), ','), ',', 1),5),4)-1)"},AT_Week={"$(=mid(trim(SubField(Concat(DISTINCT GetFieldSelections(Week_Desc), ','), ',', 1)),FindOneOf(left(trim(SubField(Concat(DISTINCT GetFieldSelections(Week_Desc), ','), ',', 1)),FindOneOf(trim(SubField(Concat(DISTINCT GetFieldSelections(Week_Desc), ','), ',', 1)),' ',2)-1),' ')+1,2))"}, Date=,MonthYear=,NumMonth=>}Volume))+

(sum({<Brand={'Total Market'},Week_Desc=, AT_Year={"$(=left(right(SubField(Concat(DISTINCT GetFieldSelections(Week_Desc), ','), ',', 2),5),4)-1)"},AT_Week={"$(=mid(trim(SubField(Concat(DISTINCT GetFieldSelections(Week_Desc), ','), ',', 2)),FindOneOf(left(trim(SubField(Concat(DISTINCT GetFieldSelections(Week_Desc), ','), ',', 2)),FindOneOf(trim(SubField(Concat(DISTINCT GetFieldSelections(Week_Desc), ','), ',', 2)),' ',2)-1),' ')+1,2))"},
Date=,MonthYear=,NumMonth=>}Volume)))-1))

In above expression the numerator is

the sum for all the volume  for Week_desc selections

and denominator is sum of volume of all those weeks in previous year

I have tried to get the denominator values modifying the getfieldselections , please suggest if the user wants n number of selections ?

Any help/suggestions will be appreciated!

[micheledenardi]

Is very hard to help you without a sample app with some sample data inside. Please share the qvf so we can play with it and find the proper solution.

[marcus_sommer · Accepted solution]

Your approach by extracting information from getfieldselections() with various aggregation- und string-functions is much too complex and unnecessary. Much easier and more common is to use pure numeric values for all kinds of matching and calculation - means the week-number would be from 1 - 52. This doesn't mean that you couldn't have the week in any string-version within the UI else that all of such string-information have also n further fields which contain the numeric information. It's quite simple to add within a master-calendar.

Also referencing to the selected values could be simplified with p() and/or e() and so your expression might be look like:

sum({< Week = p(Week), Year = {"$(=max(Year))"}>} Value) /
sum({< Week = p(Week), Year = {"$(=max(Year)-1)"}>} Value)

[Nitish91]

Thanks, i'll try to use the simpler calculations!