topic Re: Problem with expression in QlikView

Problem with expression

Thu, 28 Feb 2013 15:00:04 GMT

Hi All,

I have the following expression which counts the number of jobs that have been set to a status of 'Rejected' or 'Not Completed'.

sum({$<SystemCompletedDate={">=$(=date(Today()-1))<=$(=date(Today()-1))"},Order_QueWIPFlag ={'N'}>}If (Status_Name='Rejected'or Status_Name='Not Completed',1,0))

Here is an example of a job and its status's.

as you can see, the job was first accepted and 'Not Completed' on the 5/2/13 then reopened on the 6/2/13 and completed on 9/2/13.

but the above expression is counting the 'Not Completed' against the 9/2/13 whereas it should not be counted here as it would have already been counted on the 5/2/13.

does that make sense?

I basically want to correct this so it will not be counted on the 9th.

Can anyone help?

P.S. i am using old data here, so although the expression is counting yesterdays date, yesterday's date is actually the 9th feb.

Re: Problem with expression

Thu, 28 Feb 2013 17:12:51 GMT

any help would be appreciated

Re: Problem with expression

kaushiknsolanki — Thu, 28 Feb 2013 17:28:14 GMT

Hi,

Kindly post sample QVW file with the data.

Regards,

Kaushik Solanki

Re: Problem with expression

Fri, 01 Mar 2013 09:31:53 GMT

Hi, i have attached an example.

In the first table box, there is a breakdown of status's for the job in question.

The chart shows that this 1 job is being reported as complated and rejected, but the rejection was on the 2nd Feb so i don't want it reported here as this should be showing data for yesterday only. The rejection would have already been reported on the 2nd feb.

Any help would be appreciated.

Re: Problem with expression

kaushiknsolanki — Fri, 01 Mar 2013 11:09:32 GMT

Hi,

Please find the attched example.

Hope this is what you are looking for.

Regards,

Kaushik Solanki

Re: Problem with expression

Fri, 01 Mar 2013 12:32:37 GMT

Hi Kaushik,

Thanks for your reply.

Unfortunately, that won't work as there are around 25 different status's, around 8 of the status's change a job complete flag to 1 for complete, so i cant just use the status of 'Job Complete'.

With regards to the rejected expression, i need it to only display a count of jobs that have gone to a status of either 'rejected' or 'not completed' but only to look at the status dates which match the systemcompleted_date.

If that makes sense..

Re: Problem with expression

kaushiknsolanki — Fri, 01 Mar 2013 12:39:31 GMT

Can you please then post the qvw with the actual data and also tell us what will be the output of that, which you are looking for.

Regards,

Kaushik Solanki