topic Re: If statement including set analysis in QlikView

If statement including set analysis

Fri, 26 Feb 2016 15:35:27 GMT

I having been working with this expression for some time now and have been unable to get it to work.

=If([L.R.L. Payment - BLFLRLP] >0,(sum({$<LRL_MMonth={"$(=Date(AddMonths(Max(LRL_MMonth),-1),'MMM-YY'))"}>}[L.R.L. Payment - BLFLRLP])),(Sum([L.R.L. Payment - UBLRLP])))

What I am trying to accomplish is if L.R.L. Payment - BLFLRLP > 0 pull the payment amount from the previous month, if not sum the L.R.L. Payment - UBLRLP payment amount.

This function will need to aggregate sums up multiple levels.

Any help will be much appreciated.

Re: If statement including set analysis

hareeshkumar_gv — Fri, 26 Feb 2016 17:48:13 GMT

Hi Nathan,

try this:-

=If([L.R.L. Payment - BLFLRLP] >0,(sum({$<LRL_MMonth={"$(=month(AddMonths(Max(LRL_MMonth),-1)))"}>}[L.R.L. Payment - BLFLRLP])),(Sum([L.R.L. Payment - UBLRLP])))

Re: If statement including set analysis

Fri, 26 Feb 2016 17:55:06 GMT

Thank you for the reply. Unfortunately that did not work.

Re: If statement including set analysis

hareeshkumar_gv — Fri, 26 Feb 2016 18:04:39 GMT

could you please provide sample qvw, so that i will correct the expression

Re: If statement including set analysis

Gysbert_Wassenaar — Fri, 26 Feb 2016 18:04:57 GMT

If you're using LRL_MMonth as dimension in your chart then set analysis won't work for you. The set is calculated at the chart level, not the row level. You can't (without a great deal of trouble) show data from one LRL_MMonth in the row for another LRL_MMonth value.

Try creating a new field that associates each month with the previous month and use that field as chart dimension instead. See this blog post: The As-Of Table