null dates

[alexpanjhc]

i have a table

As_of_date,    Store,  Category,    Status, Income1, Income2, Income3

6/1/19,           A , Shoes,  A,100,50,150

6/1/19, B, Baby, A, 5,10,15

6/2/19,              A, Clothing, A, 100,-30,70

6/2/19,              B, Clothing, A, 100,-20,80

null, A, other,other,A,0,30,0

there are some entries do not have a date, because these are special entries that the Store will have to always carry, in this case, it is store A.

I am trying to build  a pivot table where we can show day by day, the sum of the income1, income2, income3.

However, the last entry (where the as_of_date is null and there are many other entries like this) can not be added as it does not belong to any dates. But I want to add the numbers to everyday, shown as

6/1/19  105, 90,195

6/2/19  200,-20,180

thanks for your help.

[Vegar]

When answering your question I will assume you have a master calendar containing a calendar key field, let's call it %Date, that is connected to your transaction table containing both dates and 'null'.

Concatenate (masterCalendar)
Load
'null' as %Date,
Date,
Month,
Year
Resident MasterCalendar;

[Channa]

create new column in load script

Autonumber(Date) as DateNum

it will generate unic numbers

in table use ur Dimension as

if (is null(Date ),DateNum,Date)

[alexpanjhc]

not sure if i am following you, but autonumber() a null value will get you a null value.

plus in my dimension it should be dates there should not be a null value at all.

[alexpanjhc]

yeah, unfortunately i do not have a calendar and i am not sure what null date's Year, Month and date should be in your script?

[Vegar · Accepted solution]

Without a calendar you can do like in my picture below and the attached qvf.

Sum(Income3)  -sum({<As_of_date >}total Income3 * isnull(As_of_date))

[alexpanjhc]

Excellent, Vegar! thank you.

Can you explain  this formula a bit  more?

when you do not give a value in the set analysis, what does that mean?

why do you use *isnull(date) , i get 0.

[Vegar]

See my comments below


sum(
{} //ignore all selections in As_of_date
total //sum over all dimensons
Income3
*
isnull(As_of_date) //If date is null then -1 else 0
)

[alexpanjhc]

OK I see. usually i use

sum({<as_of_date=>} amount) and I guess sum({<as_of_date >}  amount) is doing the same.

[Vegar]

Yes you are correct,
sum({} amount) is the same as sum({} amount).
-Vegar