Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Please visit COVID-19 group to keep up the discussion. GO TO GROUP
New Contributor III

Filter selection issue with Multiple if statement

I have written multiple if statements in two ways to create a master measure.

way 1:

pick(match(Account,'MTDAvgAUM','MTDEndAUM','YTDAvgAUM','YTDEndAUM'),Sum({<Account={'MTDAvgAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,Sum({<Account={'MTDEndAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,Sum({<Account={'YTDAvgAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,Sum({<Account={'YTDEndAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount]))

 

way 2: 

Num(
If(Account='MTDAvgAUM', Sum({<Account={'MTDAvgAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,If(Account='MTDEndAUM', Sum({<Account={'MTDEndAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,If(Account='YTDAvgAUM', Sum({<Account={'YTDAvgAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,If(Account='YTDEndAUM', Sum({<Account={'YTDEndAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount]),
sum(Amount))))),'#,##0')

 

But problem is when i am selecting account =MTDAvgAUM in filter , for rest of the if clause it giving 0 which i dont want to show. but i dont want to uncheck include zero value option also.

I want to show only result for selected account.

 

Please give me some idea.

 

Thanks

in advance.

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Filter selection issue with Multiple if statement

Since you have same set analysis for the 4 accounts and the only difference is the account itself (which the chart will handle it automatically if the dimension that you are using in the chart is "Account") then I think the below should word

Num(If(MAtch(Account,'MTDAvgAUM','MTDEndAUM','YTDAvgAUM','YTDEndAUM'),Sum({<[Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount]),sum(Amount)),'#,##0')

Yousef Amarneh

View solution in original post

4 Replies
Highlighted
Partner
Partner

Re: Filter selection issue with Multiple if statement

can you share a screenshot from the chart/table 

Yousef Amarneh
Highlighted
New Contributor III

Re: Filter selection issue with Multiple if statement

screenshot is already attached.

Highlighted
Partner
Partner

Re: Filter selection issue with Multiple if statement

Since you have same set analysis for the 4 accounts and the only difference is the account itself (which the chart will handle it automatically if the dimension that you are using in the chart is "Account") then I think the below should word

Num(If(MAtch(Account,'MTDAvgAUM','MTDEndAUM','YTDAvgAUM','YTDEndAUM'),Sum({<[Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount]),sum(Amount)),'#,##0')

Yousef Amarneh

View solution in original post

Highlighted
New Contributor III

Re: Filter selection issue with Multiple if statement

thanks a lot for help. It worked.