topic Re: Show dimension when there is no data in QlikView

Show dimension when there is no data

Wed, 20 Jun 2012 12:40:47 GMT

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.

Re: Show dimension when there is no data

Wed, 20 Jun 2012 13:06:53 GMT

I have Qlik View in spanish

try to select all fields and unclick the option indicated by the redline

I hope it help

Re: Show dimension when there is no data

Anonymous — Wed, 20 Jun 2012 13:09:40 GMT

Hi dude,

create straight table dim :'year' ,'month',expres:sum(revenue) and in the chart property >presentation>unmark 'supress zero value' block >ok.

it may solve your probloum.

Re: Show dimension when there is no data

Wed, 20 Jun 2012 15:02:05 GMT

Hi,

thanks for the quick response.

Unfortunately I've tried that before and it doesn't work for me. Maybe there is something different in my cube?

See here --> Month = January will not be displayed with the 'supress zero value' unchecked:

What am i doing wrong?

Any other ideas?

Thanks.

Re: Show dimension when there is no data

Wed, 20 Jun 2012 17:04:16 GMT

Could you add an excel with the data for me to do some tests, or you can add some info?

maybe there are some data that you miss....

Re: Show dimension when there is no data

Wed, 20 Jun 2012 17:06:13 GMT

or the qvw..

Re: Show dimension when there is no data

Thu, 21 Jun 2012 06:38:37 GMT

Thanks for response.

I added a little test file which clarifies my problem.

I mean i logically understand why the month is not showed when i filter a group of records where this month does'nt exist. But this must be possible in a way, right?

In my Test.qvw i therefore tried to link the month column in the revenue table with a month source table with all monthes in it. But this also doesn't work.

So i wanted to show every month for every year even when there is no data. But it should remain filterable.

Hope you can open the file.

Thanks.

Re: Show dimension when there is no data

Thu, 21 Jun 2012 12:22:14 GMT

Hi Florian.

I could open the qvw ok.

could you send the xls as well?

I could not reload the data...

I would delete the month inline loading, you can paste a "list" using the month that you already have.

I can adjust you own qvw

thanks

Re: Show dimension when there is no data

Thu, 21 Jun 2012 12:37:22 GMT

Hi,

sure, thanks. See the attached .xlsx. I uploaded the .qvw as well wihout the inline month definition.

I would delete the month inline loading, you can paste a "list" using the month that you already have.

How can i paste this list? What do you mean?

The month inline loading was only for testing.

Re: Show dimension when there is no data

Thu, 21 Jun 2012 14:24:31 GMT

With that qoute I just only meant that you should load the list with month like you set in the last qvw

Sorry but I don't see any zero value in the xls

I don't think that example is useful in order to experiment you issue...

Re: Show dimension when there is no data

Thu, 21 Jun 2012 14:37:26 GMT

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.

Re: Show dimension when there is no data

Thu, 21 Jun 2012 15:20:57 GMT

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.

Re: Show dimension when there is no data

Thu, 21 Jun 2012 17:49:36 GMT

Check the NullAsValue() function

Re: Show dimension when there is no data

MayilVahanan — Thu, 21 Jun 2012 19:45:34 GMT

Hi ,

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

Regards,

Mayil Vahanan

Re: Show dimension when there is no data

Fri, 22 Jun 2012 05:55:53 GMT

@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.