Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

danielco223
New Contributor III

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
Valued Contributor III

Re: if Statement based on possible field selection

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

danielco223
New Contributor III

Re: if Statement based on possible field selection

Thanks!

danielco223
New Contributor III

Re: if Statement based on possible field selection

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
Honored Contributor III

Re: if Statement based on possible field selection

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