topic Re: Replacing Null/Missing with Numeric zero for 2 dimentions in QlikView

Replacing Null/Missing with Numeric zero for 2 dimentions

Sat, 02 Aug 2014 06:26:06 GMT

Hello All,

I have a pivot table with one vertical dimension and one horizontal dimension. There is a situation where a specific combination of

dimensional values is not represented in the data but still has a cell in the pivot table. Both

dimensional fields exists in the same data model table. Hence, i am getting type of missing values with just one data model table and two dimensional fields.

look at the example below:-

In the example shown in the picture, there are amounts for all quarters in 2011 but not for Q3 and

Q4 2012. When the pivot table is calculated, an algorithm loops over all records in the database.

Since there are no data records for the last two quarters, the consequence is that the expression is

never calculated for these cells (middle and rightmost tables).

Is there a way if i could replace the MISSING(-) values with Numeric 0? May be through set analysis?

Thanks a lot for the help.

Kind Regards,

Varun

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Anonymous — Sat, 02 Aug 2014 06:35:54 GMT

Properties->Presentation->Missing Symbol(change to Zero)

If this doesn't work send me the expression you have used.we will change that to display zero

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Sat, 02 Aug 2014 06:43:49 GMT

Thanks for your response, but you know i have already tried it. Its does change missing values to 0 but still i am not able to perform any calculations on that(because this 0 is being treated as Character string), what i need here is Numeric String. I am pasting my expression below.

if(sum([TotalRevenue])='',0, sum([TotalRevenue]))

Later there is a need to change this pivot to BAR char

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Anonymous — Sat, 02 Aug 2014 06:49:11 GMT

if(isnull([TotalRevenue]),0,sum([TotalRevenue)

Try this and adjust the brackets accordingly and let me know if this works

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Sat, 02 Aug 2014 07:16:59 GMT

Still it is not working ..

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Anonymous — Sat, 02 Aug 2014 07:22:34 GMT

can you send me the sample application with the issue

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Sat, 02 Aug 2014 07:41:06 GMT

There you are, Thank you very much for spending time on this.

Re: Re: Replacing Null/Missing with Numeric zero for 2 dimentions

aveeeeeee7en — Sat, 02 Aug 2014 07:47:31 GMT

As Jebamalai said, it is giving answer when you are Missing Symbol with 0.

See the Attachment.

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Sat, 02 Aug 2014 08:39:55 GMT

Thanks for giving it a try, but i have tried this already. in your solution 0 is appearing as character string so i cannot perform any calculations using this.. I need to have numeric 0. I need to divide each column by Row totals.

Re: Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Anonymous — Sat, 02 Aug 2014 08:47:21 GMT

Here you are.

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Sat, 02 Aug 2014 10:46:16 GMT

Hello Jeba,

Thanks a lot, this is working file. However i am facing a new problem when i am converting the Pivot into Bar chart. Few Per Int are missing from the graph. Could you possibly have a look at this last piece and help me with the solution? Thank you very much again. you are genius.

Kind Regards,

Varun

Re: Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Anonymous — Sat, 02 Aug 2014 10:57:14 GMT

Hi Varun,

Not sure if this is what you were looking for.

Let me know if this doesn't met your requirement

Thanks & Regards

Jeba

Re: Re: Replacing Null/Missing with Numeric zero for 2 dimentions

aveeeeeee7en — Sat, 02 Aug 2014 11:00:20 GMT

Remove Cohort Group from the Dimesion.

Only use Per Int in the Dimension.

It will give you the desired Result.

See the Attachment.

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

qlikoqlik — Sat, 02 Aug 2014 11:22:14 GMT

Hi Varun

In excel you can do calculations on the numbers represented as text. I am sure qlikview would let you do this as well

Thanks n regards

Padma

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Sat, 02 Aug 2014 11:33:33 GMT

Hi Jeba,

Now the bar graph is showing all the Per Int, but if you compare the 20-25 per int of Bar graph vs above pivot, the above pivot has -18%, but if you look graph for the same Pet Int it is showing nothing. Please let me know if you have any questions.

Kind Regards,

Varun

Re: Replacing Null/Missing with Numeric zero for 2 dimentions

Sat, 02 Aug 2014 11:41:45 GMT

I need to use the second dimension because i need to compare the performance of different Cohorts with each other.

Re: Re: Replacing Null/Missing with Numeric zero for 2 dimentions

aveeeeeee7en — Sat, 02 Aug 2014 11:41:58 GMT

Just by simply removing the Cohort Group from the dimension, it will give you the Correct Answer for every Bucket. eg for 20-25, it is giving -18% at both places.