Set expressions vs. if statement for keeping all dimension values

Eddy — Sat, 16 Nov 2024 01:24:04 GMT

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

Re: Set expressions vs. if statement for keeping all dimension values

asinha1991 — Wed, 23 Sep 2020 17:16:22 GMT

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))

topic Re: Set expressions vs. if statement for keeping all dimension values in App Development

Set expressions vs. if statement for keeping all dimension values

Re: Set expressions vs. if statement for keeping all dimension values