Display curve with condition

[#1]

Hello,

I have a table like this one

Ref	Work Order	Work Center	Date	Total Time	Variation
A	1	WO1	2015/45	10	25
B	2	WO1	2015/45	5	14
A	3	WO2	2015/46	15	5
D	4	WO3	2015/47	2	-4
C	5	WO1	2015/45	4	5
B	6	WO2	2015/49	5	-15
A	7	WO1	2015/45	10	25

This table gives for each Work Center for each date the total time produced on this work center and the variation compared to the last 4 weeks.

I would like to display on a graph only the evolution of the production time of the work center where variation is >10 (if we are 2015/46, i want to see the evolution during the year of production of the work center where variation is >10).

How should i perform this?

[swuehl]

Could you detail how you the context of your chart should look like?

As far as I understood, you have Date as dimension, and you have a single Work Center selected.

A work center may show several Work Order per Date, so you need to aggregte the records. I guess you want to sum() the records? And filter each record by Variation > 10?

Try a dimension Date, select a Work Center and use as expression in your chart

=Sum({<Variation = {">10"}>} [Total Time])

[#3]

Hi,

Thanks for your help.

A work center will show several work order per date.

Aggregation per week & per work center is already done during the loading process, no need to sum. Total Time is really what the work center has produced during the week displayed in [date] field. In my table, the first & last row is Work Center WO1 for 2015/45, total time is the same value as WO1 produced 10h during week 45 in 2015).

I do not have any work center selected. I want the system to show the work center(s) where variation is >10 for the previous week (today, we are 2016/22, so I want to see the system to display [Total Time] evolution for all work centers where variation >10 at 2016/21).

Is it clear?

[swuehl]

I still haven't fully understood how your chart should work:

In the first two lines,you show the same work, same date, but different total time. Hence I think if you want to show in the chart e.g. a line per work center and across the dates, you would need to aggregate to take care of the multiple records (multiple work order).

Besides this, I think I would start with creating a sequential field that counts the weeks in chronological order in your script / master calendar, if you've created your calendar with the dates sorted, it could look like

=LOAD

     Date,

     Autonumber(Date) as DateNum,

     ...

Then I would create a line chart and use Date as dimension and probably [Work Center] as second dimension, which should put Date on the axis and draw a line per Work Center.

as Expression, maybe

=Avg(

{<[Work Center] = {"=Avg({<DateNum = {$(=Max(DateNum)-1)}>} Variation) >10"}>}

[Total Time])

Again, I needed to use an aggregation function because there are multiple work order lines per combination of Work center and date. If you want to filter on work order instead, replace work center with work order in above expression.

Use Aggregation Functions!

[#5]

Ref	Work Order	Work Center	Date	Total Time	Variation
A	1	WO1	2015/45	10	25
B	2	WO1	2015/45	10	25
A	3	WO2	2015/46	15	5
D	4	WO3	2015/47	2	-4
C	5	WO1	2015/48	4	5
B	6	WO2	2015/46	15	5
A	7	WO1	2015/45	10	25

Hi, thank you for your time.

You are right, my table was not realistic. I updated it to show a better example. I do not need aggregation, see the source code of this table below, aggregation is already done.

I am not very familiar with the "{", I guess it stands for a "if" condition.

=Avg(

{<[Work Center] = {"=Avg({<DateNum = {$(=Max(DateNum)-1)}>} Variation) >10"}>}

[Total Time])

[Resultat]:

LOAD

    [Order Number],

    [Operation Number],

    [Work Center],

    [Material group],

    PurgeChar([N° article],'.') as [Article],   

    weekname([Clocking date TERA]) as [date],  

    Left([Initial Time Alloc.],5) as [Time],

    PurgeChar([Total order quantity],5) as [Qty]

FROM [lib://Qlik Sense/OPbadgées_1an.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq, header is 3 lines);

//************************* Week *************************

[AggrTable]:

LOAD

    [Work Center],

    [date],

    Sum([Time]) as [TotalWeek]

Resident Resultat

Group By [Work Center],[date];

Left Join(AggrTable)

LOAD [Work Center],

weekname([date]+7) as [date],

TotalWeek as TotalWeek1

Resident AggrTable;

Left Join(AggrTable)

LOAD [Work Center],

weekname([date]+14) as [date],

TotalWeek as TotalWeek2

Resident AggrTable;

Left Join(AggrTable)

LOAD [Work Center],

weekname([date]+21) as [date],

TotalWeek as TotalWeek3

Resident AggrTable;

Left Join (Resultat)

LOAD *,

RangeAvg(TotalWeek1,TotalWeek2,TotalWeek3) as [moving average],

([TotalWeek]-RangeAvg(TotalWeek1,TotalWeek2,TotalWeek3))/[TotalWeek]*100 as [variation]

Resident AggrTable ;

Drop Tables AggrTable;

Regarding the expression, I do not get it sorry. I looked into the forum and saw this:

I think beginners would understand

If( Min(OrderDate) >= vReferenceDate, Sum(Amount) )

Rather than:-

Sum ({$<OrderDate = {'>=$(vReferenceDate)}'} Amount))

Is there a way to write it with if instead of {? Many thanks

Regards

[swuehl]

Sure, you can probably also use an if() statement instead of the so called set analysis.

Have a look at

The Aggregation Scope

Use Aggregation Functions!

A Primer on Set Analysis

[#7]

I am looking at your link but do you think you could translate your previous expression to help me? It's quite complex for a beginner

Thanks

[swuehl]

Attached is a small QS app to demonstrate what I've talked about in my earlier posts.

I am loading your second sample, but there is not really much data to show given your condition, so you might want to load your real data or more records first.

[#9]

Here is the real data. Start first the code I attached before starting the graph.

Thanks!

[swuehl]

Maybe like this