Combo chart with offset and accumulative lines

[Vince_CH]

Dear Friends,

I am making some combo chart with :

- fiscalmonth_plan as the dimension, 

- original order plan qty as bar chart,

- montly actually finished qty as one line, 

- I expect the delayed qty over month can be added into the total plan of next months, depending on the actual finished month it ends. And finally the delayed line can be like red lines as below photo showed.  Can you help here? many thx!

Also please refer to attached excel list for your further information here, with notes as following:

Delayed qty actually is dynamically based on the [Actual Finishing Date], here I just put the difference (between order-finished) into next month for explaining.

BUT, the expectation is, e.g if the fiscalmonth([Actual Finishing Date]) is 3 months later than Fiscalmonth_plan, then the total plan in following three months shall all contains this delayed quantity as too.

[Vince_CH]

@swuehl，I have tried exactly as the sample as following but didn't get an desired result yet, kindly please support here.

My scripts like following:

DateRange:

LOAD

[Production Order No.],

[Required Date],

…

Alt([Actual Finishing Date],Today()+1) as [Actual Finishing Date]

Resident [Order Plan];

Link:

LOAD

MonthName(ReferenceDate) as ReferenceMonthName

WHERE ReferenceDate <= Today();

LOAD

[Production Order No.],

[Required Date],

Date([Finalized Date]+iterno()-1) as ReferenceDate,

[Actual Finishing Date],

If([Actual Finishing Date]> Monthend([Finalized Date]),'OpenFlag','EndFlag') as Type,

If(iterno()=1, 1,0) as PlanFlag

Resident DateRange

WHILE [Finalized Date]+iterno()-1 <= [Actual Finishing Date];

----------------------------------------------------------------------------------

In the chart, the dimension is  ReferenceMonthName, with measure expression as below: 

Count(DISTINCT{<[Order Category]={'Machine'},PlanFlag={1}>}[Production Order No.]),

so I get a chart like following with error,

[swuehl]

Could you post a small sample QVF with some sample input data? 

If you select a single production order number, does Link data look like expected? It's often easier to focus on a single case first, then look at the aggregate.

Coming back to your question how to identify plan / resolved dates, you can do this by creating flags in your link table on these dates.

As far as I remember, I did the same in one of the threads I referenced in my previous post (or in a thread that was linked in these threads).

[Vince_CH]

@swuehl thanks for reply. 

I have pulled my hair off and resolved the issue now. however, your direction of CanonicalDate and reference date is correct. (also many thanks to original posts of @hic here, benefial a not). 

in my case, it is a little tricky that the [actual finished date] could be sometimes earlier than the [required date] for certain orders, that means to use reference date is not able to count orders which include delayed and earlier finished orders.  So i use canonicalDate in different DateType  combined with referencedate to count both finally. with dimension in monthname(CanonicalDate).  the solution is quite heavy stuff, and I am happy it finally works.