Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Vince_CH
Contributor II

Re: Combo chart with offset and accumulative lines

@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,

11.jpg

Highlighted
MVP
MVP

Re: Combo chart with offset and accumulative lines

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
Contributor II

Re: Combo chart with offset and accumulative lines

@swuehl thanks for reply. 

I have pulled my hair off and resolved the issue now. however, your direction of CanonicalDate and reference date is correctSmiley Happy. (also many thanks to original posts of @Henric_Cronström 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 worksSmiley Happy