Omitting Zeros in Charts

[#1]

Hi,

I have a problem trying to define values in a chart in Qlikview. Here's the story:

I have a report that moves from through different stages; received --> pending --> filed. As the report moves from one stage to the next it gets an date stamp when it gets the new status. A filed report will as a result have a received, pending and a filed date. A pending report, on the other hand, will only have a received and a pending date, where the filed date will show up as 0. This is all set in the SAP data base from where I load the data.

Now, the problem is that when i set up a chart for all reports with the status "pending" over a set years, I will get a huge bar at year 0 due to the fact that it will then calculate all the reports with a year 0, that is all reports that has yet to be "filed".

What I would like to do here is to is to include a conditional expression in the chart saying something like:

WHEN

received_date <> 0 AND

pending_date <> 0 AND

filed_date == 0

INCLUDE VALUE

(mind you here that I dont really know how all the syntaxes work yet )

I just dont know how to do this. I cannot change the way the numbers are loaded into Qlikview - that's just not an option due to internal restrictions. I have been thinking about creating a sort of load table to sort the values, but I cant seem to that right either.

Thanks!

[#2]

Try below IF Condution:

IF(RECIVED_DATE <> 0 AND PEDNING_DATE <> 0 AND FILED_DATE = 0 , INCLUDEVALUE)

[#3]

thank you for the quick respons, however it didnt work. I think the problem can be with the whole IF statement, since it will (to my knowledge) quit after it finds its first correct value.

I think I need something more of a SELECT statement, however I am not sure about this either...

[#4]

Where can you need this statement ? if its is script, please post some sample script. so we can help easily

[Nicole-Smith]

In your chart, you can use set analysis inside of your calculation.  The set analysis would look like:

{<field_date={0}>-{received_date={0},pending_date={0}>}

So your whole expression would look something like:

sum({<field_date={0}>-<received_date={0},pending_date={0}>} value)

[#6]

Or you can make use of a if statement in your expression.

Say your expression uses a sum function, then write something like:

sum(if(received_date <> 0 and pending_date <> 0 and filed_date = 0,INCLUDE VALUE))

[#7 · Accepted solution]

thank you all for you replies, however i managed to solve this by some trial an error coding.

i put in a new expression for the graph under the expressions tab:

COUNT(DISTINCT IF(pending_date<> 0,value))

this seemed to solve my troubles.

cheers!