Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Replacing Null/Missing with Numeric zero for 2 dimentions

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:-

missing.png

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

Tags (1)
16 Replies
jebamalai
Valued Contributor

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

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

Not applicable

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

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

jebamalai
Valued Contributor

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

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

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

Not applicable

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

Still it is not working ..

jebamalai
Valued Contributor

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

can you send me the sample application with the issue

Not applicable

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

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

aveeeeeee7en
Valued Contributor III

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

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

See the Attachment.

Not applicable

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

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.

jebamalai
Valued Contributor

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

Here you are.

Community Browser