Master Dimension - IF Statement logic...

[jerryr125]

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

[rwunderlich]

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

[jerryr125]

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

[rwunderlich]

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

[jerryr125 · Accepted solution]

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