Dealing with Null Value in Dimension

[Former Member]

I have a table like this.

The database does not have 2016-04-02.

There was no sales on that particular day.

I want the table to look like this:

2016-04-01  200

2016-04-02  0

2016-04-03 150

I tried 1 solutions below.

Format Table->Show Rows with empty dimension values.

But this does not Work

Can anyone please give me ideas how I may overcome this problem?

[Former Member · Accepted solution]

Hi Hayden,

Could you please try TimeDim([DateObject]) function. Basically it gets all the missing date with defined period.

Regards

Niraj

[Former Member]

Hi Hayden,

there is three ways to deal with your problem

Option 1) Your sales table is inner joined with time dimension table so you are not getting dates where there is no record in sales table.

So if you do a left outer join on time dimension you will get all the dates

Option 2) In report level, create one more data provider with date object and merge the dates of initial dp and this new dp. Then also you get all the dates

Option 3) Use the function TimeDim([date];dayperiod) this also gives all the missing dates (as Neeraj mentioned). Then you need modify the measure saying if(isnull(sales);0;sales)''

Hope this helps. choose the option which is suitable in your sitution

[Former Member]

Thank you so much!!!

Option 2 worked for me.

[Former Member]

Glad that it worked.

Hope you have put all the time related filters which are in your first data provider also in the new dp.

suppose if your first dp has year filter then put the same filter in 2nd dp  to get only required dates.

Close this thread if you got the answer.

[Former Member]

Thank you.

Regarding Option 3) I get multivalue error.

I am leaving this discussion open in case somebody might be able to help me...

[amitrathi239]

what is the qualification of you Date object? dimension or measure?

Ideally you will not get the  multi-value error if your object/variable is Dimension.

[Former Member]

Hello Hayden,

What is the source of the Webi ? Is it BEx?

If So where you are getting the #MultiValue Error if on measure could you please confirm if your measure is Delegated Measure or Smart Measure?

Regards

Niraj

[Former Member]

Thank you.

I tried Dimension but I get error also.

[Former Member]

I do not work with back-end.

I will ask back-end team...

Thank you...

[Former Member]

Here's a document that should help you with this in detail.

Regards

Niraj

[Former Member]

Thank you!

1. I did not know such function existed.

2. I tested but I get #multivalue error.

I do not have access so I do not know the solution.

If you happen to know the solution for this, could you please let me know...?

http://solveissue.com/note?id=1877288