Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Eddy
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: 

Data:
Load
*,
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
];

Calendar:
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, 

Eddy

1 Reply
asinha1991
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 null...it 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))