How to compare if item appear at last week ?

[HenryHeung]

Hi all , 

I am a beginner in Qilk Sense and i am working in Manufacturing industry right now . I am going to conduct an analysis for style change over rate for each production line in the factory per week .  I want to know if the style under production at the current week is also under production last week .  How can i add an column to determine if it is the case in Qilk Sense 

source data :

production line	week	Style
A	25	S4
A	25	S5
A	25	S6
A	25	S7
A	26	S1
A	26	S2
A	26	S3
A	27	S1
A	27	S2
A	27	S4

desirable outcome : 

Since there is no week 24 in the source file, i would like to set all styles at week 25 is 0 .

Since S1 and S2 also appear at week 26 , So state 1 for them at week 27.

production line	week	Style	Appear at last week
A	25	S4	0
A	25	S5	0
A	25	S6	0
A	25	S7	0
A	26	S1	0
A	26	S2	0
A	26	S3	0
A	27	S1	1
A	27	S5	0
A	27	S2	1
A	27	S4	0

your help would be deeply appreciated . 

[HenryHeung]

Hi all ,

may someone help me a bit ? or i need to provide additional information to comprehend my question ?

[anseglko · Accepted solution]

Does this work for you?

if(aggr(week=above(week)+1,Style,week),1,0)

 

[HenryHeung]

Hello Anseglko,

Thank you for your big help 

Henry

[HenryHeung]

Hi anseglko,

May you explain how does it formula work ? 

it is because when try to sum (if(aggr(week=above(week) +1, Style,week),1,0)) by line , the answer is wrong , the correct answer is 7 .

if i only filter the data in P1T1A , the answer is correct (7 ) 

How can i make the summary by line such that the answer is correct ? i think i do not understand the logic of this formula ？ Could you explain it a bit ? Thank you.

[anseglko]

Hi,

The formula first groups the results by style, then by week. Then it checks if the week on that line is the same as on above line. For example after the aggregation it might look like this

Production line	Style	Week	function
A	S1	26	0
A	S1	27	1 (because 27 is 26+1)

...

A	S2	0	0
A	S2	1	1

...

A

S3

0

0

and so on.

I'm not sure why your function fails. Maybe try to add: sum(if(aggr(week=above(week) +1, production_line, Style,week),1,0))

Or can you show the style and week in the table as well and post a screenshot here?