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.

[zzyjordan]

Hi, Vince
Not sure what you mean by "3 month later than fiscalmonth", can you give an example? also, what's your data looks like ?

ZZ

[Vince_CH]

@zzyjordan:

thanks for your time.

let me explain, e.g. in case of one order with [required date] as 2018/09/05, but it was actually finished on 2018/12/15. So besides the original planned orders monthly, for following planned months like Oct, Nov, Dec, shall all contains this delayed order qty as delayed qty. hope it make sense now.

[Vince_CH]

also, here is the looking of my data, thanks

[Vince_CH]

Dear All,

Do you have any further advices here?

@zzyjordan

@OmarBenSalem

@sunny_talwar

thanks.

[Vince_CH]

Dear Experts,

I am still stuch with above question, can someone help me out here?

If to add the delayed orders all into next month would be using above(), but here delayed orders could possilbly span over serveral following months, which is my bottleneck here, any funtions or expression can be used?

If any questions, kindly please let me know. 

Very appreciated for the support!

@swuehl

@hic

@Gysbert_Wassenaar

@rwunderlich

@Patrik_Lundblad

[swuehl]

Maybe have a look at my answer in

Show Closed and opened case in a single chart

[Vince_CH]

@swuehl
thank you, is reading these articles, and thinking how to adapt to my situation.
will consult if any questions, thanks!

[Vince_CH]

@swuehl,
Sorry for some silly questions:
Having read the posts, still confused about how to convert the sample dimensional date into my fiscal month instead? also if "Alt(Ticket_ResolveDate,Today()) as EndDate", then how to differentiate between the still open case and closed case maybe just on today?
My scripts are like following, if convenient would you be so kind to support with the further writings ? thanks!

SET vFM=7;

[Order Plan]:
LOAD
[Production Order No.],
[Required Date],
[Actual Finishing Date],

Dual(Year([Required Date]) + If(Month([Required Date])>= $(vFM) , 1, 0), Year([Required Date]) + If(Month([Required Date])>= $(vFM) , 1, 0)) as FiscalYear_Plan,

Dual(Month([Required Date]), Mod(Month([Required Date])-$(vFM), 12)+1) as FiscalMonth_Plan,

Dual(Year([Actual Finishing Date]) + If(Month([Actual Finishing Date])>= $(vFM) , 1, 0), Year([Actual Finishing Date]) + If(Month([Actual Finishing Date])>= $(vFM) , 1, 0)) as FiscalYear_End,

Dual(Month([Actual Finishing Date]), Mod(Month([Actual Finishing Date])-$(vFM), 12)+1) as FiscalMonth_End

FROM [lib://QVD 6/Order_ Plan_2017_V3.xlsx]
(ooxml, embedded labels, table is Database)

[Vince_CH]

Dear expert,  this issue has been pending for near 2 weeks for me , I believe ceating reference dates would the solution here. but how to use iterno() here for the dimension in fiscalmonth instead of day in the scripts writing in details? kindly please direct and intruct here, thanks in advance