Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

How to compare if item appear at last week ?

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 
A25S4
A25S5
A25S6
A25S7
A26S1 
A26S2
A26S3
A27S1 
A27S2
A27S4

 

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 
A25S40
A25S50
A25S60
A25S70
A26S1 0
A26S20
A26S30
A27S1 1
A27S50
A27S21
A27S40

 

your help would be deeply appreciated . 

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Does this work for you?

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

this.png 

View solution in original post

5 Replies
Highlighted
Contributor
Contributor

Hi all ,

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

Highlighted
Creator
Creator

Does this work for you?

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

this.png 

View solution in original post

Highlighted
Contributor
Contributor

Hello Anseglko,

Thank you for your big help 

 

Henry

Highlighted
Contributor
Contributor

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 .

clipboard_image_6.png

 

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

clipboard_image_4.png

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.

Highlighted
Creator
Creator

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 lineStyleWeekfunction
AS1260
AS1271 (because 27 is 26+1)

...

AS200
AS211

...

AS300

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?