Condition in Chart

[girish_talele]

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.

[Gysbert_Wassenaar]

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

[alkesh_sharma]

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.

[jagan]

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.

[girish_talele]

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 -

1. 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,
2. If there are more than two rows in which above condition is true still single count shall be updated in Column "Before Date",
3. 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.

[girish_talele]

Dear Experts,

Is there any way out???

Regards,

Girish.