How to handle Null Value in QlikView Star Schema Model? and dealing with 2 dates Created Date and Completed Date

[#1]

Hi Guys,

I have a fact which is having 2 date keys with 2 separate date dimensions Created Date and Completed Date

When Order has been created but not yet completed for those records Completed date will be Blank and in My dashboard, I have to use both Created as well as Completed date to filter data

When I try to browse my data It is always showing records which are completed. It is not showing record which are not yet completed.

How I have designed my dashboard is by Creating a Input box named as Date Type and hardcoded 2 values Created Date and completed Date in dropdown list.

Now selected a list box and used below expression to get Created year and completed Year.

=IF(vDateType='Created Date',[Created Year],[Completed Year])

Same way to get Created and completed Quarter, Month and Day of month

So when I select Date Type Created Date from dropdown then my dashboard should display data for Created year, Quarter, month and Day.

When Date Type Completed Date from dropdown then my dashboard should display data for Completed year, Quarter, month and Day.

But It is not working as expected.

See screenshot7

When I select  Date Type=Created Date and I select Year 2015 my current selection box is displaying that I have selected both Created Year and completed Year. How this possible?

See Screenshot 8

Message was edited by: Shivendoo Kumar

[awhitfield]

Hi Shivendoo,

can you post an example qvw?

Andy

[#3]

Please find attached Sample qvw

Data Explanation:

Order Count by Created Date on 26th Apri-2015 is 143

Order Count by Completed Date on 26th Apri-2015 is 56

If I select Date Type=Created Date

and then select Year=2015, Month=Apr Day=26, 27

I am getting count 56 but I am expecting 143

If you see current selection box: It is displaying Created Year=2015 and Completed Year=2015 but I have selected just Created Year, not Completed Year.

If I clear Completed related selection from current selection box then I am getting count 143.

Why this approach is not working? what would be alternate way to achieve this?

[#4]

Experts, Please could you guys respond to this

[swuehl]

I think you need to expand the field Name in the field Expression in your list box, instead using an if condition that will be executed per row of your table.

To demonstrate, I've created two new variables for year and month list box field selection.

See attached.

[#6]

Swuehl,

Thanks for your reply.

Actually, I also want to clear Created Year, Quarter, Month and Day selections when I change from Date Type Value from Created Date to Completed Date and Vice Versa.

I dont want clear any other selections other that these 4 Year, Quarter, Month and Day.

Suppose I have selected Date Type=Created Date and Selected Year=2015, Quarter=Q2, Month=Apr, Day=26

and suppose I have another selection Country and I have selected Country=Australia

Now I change Date Type=Completed Date, When I do this Created Year=2015, Created Quarter=Q2, Created Month=Apr, Created Day=26, selection should clear automatically but It should not clear the Country=Australia selection

I am not sure How to do this? Appreciate you guidance.

[swuehl · Accepted solution]

You can create a variable Event Trigger with several Actions in Settings - Document properties to clear selections in These fields when the variable changes.

[#8]

Hi Swuehl,

It works perfectly.

I learnt one lesson:

Create a action with = Singe  It is not working.

See attached screenshot:

Thank you so much.