Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

if Statement based on possible field selection

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?

4 Replies
krishna_2644
Specialist III
Specialist III

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]))

Anonymous
Not applicable
Author

Thanks!

Anonymous
Not applicable
Author

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

However, if I selected April and October, it does not work.

Multi.jpg

Any ideas? I need the expression to identify the current month in all the selections (in this case, Apr), and return true.

sasiparupudi1
Master III
Master III

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]))