Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Specialist

## null dates

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

1 Solution

Accepted Solutions
MVP

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))``
10 Replies
MVP
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)
'null' as %Date,
Date,
Month,
Year
Resident MasterCalendar;
Specialist III

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)

Channa
Specialist
Author

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.

Specialist
Author

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?

MVP

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))``
Specialist
Author

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.

MVP

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

OK I see. usually i use

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

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