Skip to main content
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Set expressions vs. if statement for keeping all dimension values

Hello experts, I have a question (example QVF attached) .

I want to have a simple table with dimension DateCalendar and key figure Count({<Flag={'1'},Status={'A'}>} ID)

I want my dimension DateCalendar  to show all dates from 20200901 to 20200910 without gaps. So I use DateCalendar as dimension. 

1) I use key figure Count({<Flag={'1'},Status={'A'}>} ID)  

why do I have Null value in my dimension in the table object? I do not have Null value in Dimension in my data 

-> Here I only have date value for which I have data in my Data table


2) I use key figure =if(Flag = 1 AND Status = 'A', Count(ID),0) and Flag and Status fields in my table

-> why do I have Null value in my dimension in the table object? I do not have Null value in Dimension in my data

-> I have all dates as dimension values, that is what I want


3)   I use key figure =if(Flag = 1 AND Status = 'A', Count(ID),0) 

-> I have only correct calculation for date 20200901, that is where I have only one data point, why does my key figure evaluate to 0 for the others? 


What is the best solution to have the key figure Count({<Flag={'1'},Status={'A'}>} ID) and all dimension values for DateCalendar in a table object? 


my Data: 

Date as %DateKey;
Load * Inline [
Date, Flag, Status, ID
20200901, 1, A, 1

20200903, 1, A, 1
20200903, 1, B, 1
20200903, 0, A, 1

20200905, 1, A, 1
20200905, 0, A, 1

2020910, 1, A, 1
2020910, 1, B, 1
2020910, 0, A, 1
2020910, 1, A, 1

Load * Inline [
%DateKey, DateCalendar
20200901, 20200901
20200902, 20200902
20200903, 20200903
20200904, 20200904
20200905, 20200905
20200906, 20200906
20200907, 20200907
20200908, 20200908
20200909, 20200909
20200910, 20200910

Thank you, 


Labels (3)
1 Reply
Creator III
Creator III

for 1 and 2 , create a table with datecalendar date, and %datekey field and see if you if any place where datecalendar is could be because your datekey is nothing at some place...

for 3 you dont have 1 to 1 mapping between flag/status and date, unless flag and status are dimension it wont work...either use set analysis or put if inside count like count(if(flag='1' and status='A',ID))