Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am trying to create an if expression that considers the possibility of a field being selected. See below
I would like the expression to evaluate based on the context of the month(s) selected. For example, if two prior months are selected, simply add the full amount of the months. But if the current month and a prior month is selected,I need the expression to only add the current month to date sum and the full prior month. These sums are based on prior year data so if you dont specify "to date", it adds the full amount for the month.
I created a variable called (vCurrentPeriodName) that always returns the current month (in this case, "Apr")
I need the expression to recognize when April (current month) is one of the possible selections and perform the first measure in the if statement. If not, 2nd measure.
if(GetSelectedCount([Fiscal - Month])>1,
Sum({$<[Fiscal - Flag Prior Month YTD]={'Y'}>}[FY17 Baseline Amount])
+
Sum({$<[Fiscal - Period Name]-={'(vCurrentPeriodName)'}>}[FY17 Baseline Amount])
,
Sum([FY17 Baseline Amount]))
Any thoughts?
Can you please post some sample data?
unless i have data and verify it,i cannot confirm the below expression works.
u can give this expression a try:
if(GetSelectedCount([Fiscal - Month])>1,
if(
match(concat(getfieldselection([Fiscal - Month]) ,',' ) ,'$(vCurrentPeriodName)') =1 ,
Sum({$<[Fiscal - Flag Prior Month YTD]={'Y'}>}[FY17 Baseline Amount])
+
Sum({$<[Fiscal - Period Name]-={'(vCurrentPeriodName)'}>}[FY17 Baseline Amount])
)
,
Sum([FY17 Baseline Amount]))
Thanks!
Actually, having an issue where it is not finding the (vCurrentPeriodName)) as a possible value in the selections. For example, using the below expression....
match(concat(getfieldselections([Fiscal - Period Name]),',') ,(vCurrentPeriodName))
...if April is selected, then the expression works since we are in the current month.
However, if I selected April and October, it does not work.
Any ideas? I need the expression to identify the current month in all the selections (in this case, Apr), and return true.
May be this?
if(GetSelectedCount([Fiscal - Month])>1 AND Index(GetFieldSelections([Fiscal - Month],','),'(vCurrentPeriodName)')>0,
Sum({$<[Fiscal - Flag Prior Month YTD]={'Y'}>}[FY17 Baseline Amount])
+
Sum({$<[Fiscal - Period Name]-={'(vCurrentPeriodName)'}>}[FY17 Baseline Amount])
,
Sum([FY17 Baseline Amount]))