topic Re: Condition in Chart in QlikView

Condition in Chart

girish_talele — Wed, 24 Dec 2014 06:33:59 GMT

Dear Experts,

I have data as shown below, I am comparing "Req Date v/s Actual Date".

While comparing at Order level I am getting correct results But while comparing at Section Level the results are not matching.

This is happening due to some orders qualifying both condition of "Before Req Date" as well as "On Req date".

Please suggest syntax of formula to achieve the results for second chart.

Re: Condition in Chart

Gysbert_Wassenaar — Wed, 24 Dec 2014 06:52:09 GMT

Maybe this: Count(If(ReqDate = aggr(max(ActualDate),Section,Order),Order))

Re: Condition in Chart

alkesh_sharma — Wed, 24 Dec 2014 06:54:11 GMT

Girish,

Look at the data again. Sec1 has 3 orders on which request date is equal to actual date.

So your calculations is right.

Select one order and that will give you how many of that order arrived on time and or before time...

Every thing is sec1 in your data.

Re: Condition in Chart

jagan — Wed, 24 Dec 2014 07:02:56 GMT

Hi,

Create a key field in your table using Recno() like below

Data:

Load

RecNo() AS Key,

INLINE [

Section, Order, Desc, ReqDate, ActualDate, Qty

Sec1, 1, A 1-1-1, 22/12/2014, 21/12/2014, 10

Sec1, 1, A 1-1-1, 22/12/2014, 22/12/2014, 10

Sec1, 1, A 1-1-1, 22/12/2014, 23/12/2014, 10

Sec1, 2, B 2-1-1, 22/12/2014, 20/12/2014, 10

Sec1, 2, B 2-1-1, 22/12/2014, 21/12/2014, 10

Sec1, 2, B 2-1-1, 22/12/2014, 22/12/2014, 10

Sec1, 2, B 2-1-1, 22/12/2014, 23/12/2014, 10

Sec1, 3, C 0-1-0, 22/12/2014, 22/12/2014, 10

Sec1, 4, D 2-0-0, 22/12/2014, 21/12/2014, 10

Sec1, 5, E 0-0-1, 22/12/2014, 23/12/2014, 10

];

And use below expressions:

Before Req. Date - Count(If(ActualDate < ReqDate,Key))

On Req. Date - Count(If(ActualDate = ReqDate,Key))

After Req. Date - Count(If(ActualDate > ReqDate,Key))

Hope this helps you.

Regards,

Jagan.

Re: Condition in Chart

girish_talele — Wed, 24 Dec 2014 07:34:39 GMT

Dear Gysbert Wassenaar, Alkesh, Jagan Mohan,

Thanks for looking into.

Dear Jagan Mohan - I want to calculate the same in chart only because there are several other variable in condition which user will enter.

May be I was not able put it across correctly, Following are the conditions -

If the Actual Date is less than Req. date then the Column "Before Date" shall be updated, No updation of "On Req date" even if there is another row where Actual Date equal to Req. Date,
If there are more than two rows in which above condition is true still single count shall be updated in Column "Before Date",
If condition of "Before Date" is not meeting AND Actual Date equal to Req. Date then only the column "On Req date" shall be updated.

First two conditions are achieved but third is not.

Please find the attached file with more data.

Re: Condition in Chart

girish_talele — Wed, 24 Dec 2014 13:44:13 GMT

Dear Experts,

Is there any way out???

Regards,

Girish.