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

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

Mon, 27 Apr 2015 11:34:47 GMT

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

Re: How to handle Null Value in QlikView Star Schema Model? Created Date and Completed Date

awhitfield — Mon, 27 Apr 2015 15:12:40 GMT

Hi Shivendoo,

can you post an example qvw?

Andy

Re: How to handle Null Value in QlikView Star Schema Model? Created Date and Completed Date

Tue, 28 Apr 2015 00:17:57 GMT

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?

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

Tue, 28 Apr 2015 06:33:29 GMT

Experts, Please could you guys respond to this

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

swuehl — Tue, 28 Apr 2015 08:12:22 GMT

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.

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

Tue, 28 Apr 2015 08:37:40 GMT

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.

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

swuehl — Tue, 28 Apr 2015 09:25:19 GMT

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

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

Wed, 29 Apr 2015 00:14:56 GMT

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.