topic Re: Master Dimension - IF Statement logic... in App Development

Master Dimension - IF Statement logic...

jerryr125 — Thu, 29 Jul 2021 17:22:55 GMT

Hi -

I created the following if statement for a master dimension (mdStatus):

=IF(pomg='Y','Revised',IF(DCIndicator='X',IF([LRDate.autoCalendar.Date]>$(vNDDate),'Past Due','On Time'),IF($(vNDate)<Today(),'OO - Past Due','OO')))

Therefore, the final values for the master dimension is:

Revised
Past Due
On Time
OO - Past Due
OO

Even though there is no issues in the expression of the IF statement, when it comes to the visualization the filtering and display is inaccurate.

For example, if you filter mdStatus on "On Time" the visualizations show both "On Time" and "Past Due".

Any thoughts on why the logics is not functioning?

mdStatus should only have one status.

Thanks - Jerry

Re: Master Dimension - IF Statement logic...

rwunderlich — Thu, 29 Jul 2021 18:12:04 GMT

Selections are made only in fields. When using a calculated dimension, there can be some implicit selections made that are not what is intended.

You should explicitly declare what field this Dimension should select by wrapping the expression in Aggr(). For example, to make the selections in field ID:

Aggr(=IF(pomg='Y','Revised',IF(DCIndicator='X',IF([LRDate.autoCalendar.Date]>$(vNDDate),'Past Due','On Time'),IF($(vNDate)<Today(),'OO - Past Due','OO'))), ID)

For more, see https://qlikviewcookbook.com/2016/01/scoping-selections-with-aggr/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Master Dimension - IF Statement logic...

jerryr125 — Thu, 29 Jul 2021 19:59:14 GMT

Hi - Thanks for your help.

I am using Qlik Sense Not Qlik View.

I did the following :

1. Created a field in my main table titled "ABSTATUS" - with no values.

2. I changed the formula to the following:

Aggr(=IF(pomg='Y','Revised',IF(DCIndicator='X',IF([LRDate.autoCalendar.Date]>$(vNDDate),'Past Due','On Time'),IF($(vNDate)<Today(),'OO - Past Due','OO'))), ABSTATUS)

Still does not work and now all values of mdStatus are set to 'Revised'.

Thoughts ? I feel I am close! Jerry

Re: Master Dimension - IF Statement logic...

rwunderlich — Thu, 29 Jul 2021 21:58:55 GMT

QlikView and Qlik Sense behave the same in this case. The blog post was written for QlikView, but applies equally to QS.

Your ABSTATUS field should have values. The idea is you would use this field to select rows in your data. Something like an id field.

-Rob

Re: Master Dimension - IF Statement logic...

jerryr125 — Mon, 02 Aug 2021 21:11:04 GMT

Hi - thank you again for your reply and assistance. All set - I am doing the logic in the load script - works like a charm! thanks - Jerry