Combo chart with double lines

[Vince_CH]

Dear All,

I have some excel table as following for production order control, with red cross is the field manually added in for explaining.

With the expetation is to get some combo chart as below right side illustrated, with monthly finished orders in bar, and originally plan qty in blue line, plan plus the delayed qty in total as the acculative line. 

The logic for calcualatin would be:

-if fiscalmonth of [actual finishing date] is same or smaller than fiscalmonth of [required date], it will be counted as monthly finished orders. 

- if fiscalmonth of [actual finishing date] is greater than fiscalmonth of [required date], it is counted as delay

- if [actual finishing date] is empty, fiscalmonth(today()) is used to compare with fiscalmonth([required date]), if fiscalmonth(today())-fiscalmoth(required date))>0, then it is counted as delay too.

- Top line on the chart would be the acumulative sum of original plan of current month plus the delayed quantity from previous month.

Appreciated for your help, thanks!

[OmarBenSalem]

Wouldn't be better to share ur excel file?

So that we can work with? 

[Vince_CH]

@OmarBenSalem

Thanks for your time, I made some data similarly as attached list for simulation purpose. 

1. If Actual finished date in same month as required, counted as finished qty in bar.

2. If fiscalmonth of finished is greaten than required month, counted as delay.

3. If not finished, today() is used to compare in month, if greater, counted as delay too.

4. Total plan qty of the month would be the sum of the plan, plus the delay qty from previous month.

5. all months are in fiscal month starts since July still June. 

[OmarBenSalem]

1) In the script:

load * ,
if(month("Actual Finishing Date")= month( "Required Date"),'Finished Qty',
if(("Actual Finishing Date")<> ( "Required Date") ,'Delay')) as Type;
LOAD
if(len(trim("Actual Finishing Date"))=0,Today(),"Actual Finishing Date") as "Actual Finishing Date" ,
month( "Required Date")&'-'&Year("Required Date") as MonthYear,
"Production Order No.",
"Required Date"

FROM [lib://DATA_SOURCES/12.xlsx]
(ooxml, embedded labels, table is Sheet1);

2) In presentation : 

as dimension: MonthYear

as Measures:

Finished Qty: Count({<Type={'Finished Qty'}>}Type)

Delay : count({<Type={'Delay'}>}Type)

Total : aggr(Count({<Type={'Finished Qty'}>}Type)+ above(count({<MonthYear, Type={'Delay'}>}Type)), MonthYear)

Result:

If I select Jul and August so that u can see clearer :

Omar BEN SALEM

[Vince_CH]

@OmarBenSalem:

Thank you very much for the efforts, I have modified the contents to fit my situation:

- my dimension is fiscalmonth_plan, plan is the required date.  

- measures of original plan: Count(DISTINCT{<[Order Category]={'Machine'}, FiscalYear_Plan={'$(=Max(FiscalYear_Plan)-1)'}>}
[Production Order No.])

- measures of monthly plan finished qty: Count(DISTINCT{<[Order Category]={'Machine'},FiscalYear_Plan={'$(=Max(FiscalYear_Plan)-1)'},Type={'Finished Qty'}>}
[Production Order No.])

BUT, the chart comes up looking like following instead, which is not right, because we are not having production output yet in Jan, Feb, Mar, onwards yet.   The scripts is also attached here , kindly please advise, thanks!

SET vFM=7;

[Production Plan]:

LOAD

         [Production Order No.],

    [Required Date],

    If(Len(Trim([Actual Finishing Date]))=0,Today(),[Actual Finishing Date]) as [Actual Finishing Date],

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

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([Actual Finishing Date]), Mod(Month([Actual Finishing Date])-$(vFM), 12)+1) as FMonth_Pack,

FROM [lib://QVD 6/Order_Production Plan_2017_V3.xlsx]

(ooxml, embedded labels, table is Database);

LOAD

If(FMonth_Pack<=FMonth_Plan, 'Finished Qty', 'Delayed') as Type

Resident [Production Plan];

[Vince_CH]

Hello All, can you advise and help me out on this? thx.

[Vince_CH]

@OmarBenSalem:

Do you have any further advices here? thanks!