Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!
Try below IF Condution:
IF(RECIVED_DATE <> 0 AND PEDNING_DATE <> 0 AND FILED_DATE = 0 , INCLUDEVALUE)
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...
Where can you need this statement ? if its is script, please post some sample script. so we can help easily
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)
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))
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!