Show dimension when there is no data

[#1]

Hi all,

let's say i have a pivot-table with the dimensions year and month and an expression which calculates the revenue.

Now i wanted to show the dimension 'month' even when there is no data. BUT it should stay filterable. So the 'Show all values'-Option is not an option for me.

So i wanted to display it like this:

Year     Month     Revenue

2012     01          100

2012     02          150

2012     03          0               //show dimension even when there is no data

2012     04          350

...

And when i e.g. filter month = 04 then the result should look like this:

Year     Month     Revenue

2012     04          350

and NOT LIKE this (same filter)

Year     Month     Revenue

2012     04          350

2012     01          0

2012     02          0

2012     03          0

Hope you get what i mean.

Is that even possible?

I've previously searched for a solution on the internet (inclusive forum) but without any success.

Thanks in advance.

[#2]

Ahh ok

Yes you're right. There are no zero values in my xls. And thats exactly the problem.

If there were zero values in combination with all months then my problem would be solved

I wanted the dimensions to display even when there is NO DATA.

But fact is i have only data in my database when there is a value. And to generate this zero values with all combinations can not be the solution.

Hope you understand would i mean.

Thanks.

[#3]

I guess you have problems when you are loading the data from the database. if there are zero values there and you don't see it in the table you should check in the load...

I changed the excel that you sent and I get the zero values, so your problem is not in the setup of the pivot table.

[#4]

Check the NullAsValue() function

[MayilVahanan]

Hi ,

     Please see the below file, i think its satisfy your condition.

     Regards,

     Mayil Vahanan

[#6]

@luchomaxis

Can you please send me the modified xls so i can check what i had to change.

I will check the NullAsValue()-Function during the weekend.

@Mayil Vahanan

Thanks for this. But what if there is another dimension before the month in the pivot table. Then i had to add another concatenate to create combinations for this, right?

Thanks to you.