Checking number of weeks in specific status

[#1]

Hello,

We have a simple project structure with a column which shows the status of this project.

There is a need to add a column which will show for how many weeks project was saved under specific status.

Example table:

Project	Week	Flag	Number of weeks not in green
A	15W1	White	0
A	15W2	Green	0
A	15W3	Yellow	1
A	15W4	Yellow	2
A	15W5	Red	3
A	15W6	Green	0
B	15W4	Red	1
B	15W5	Green	0
B	15W6	Red	1
C	15W5	Green	0
C	15W6	Green	0

Column 4 is representing expected outcome in QlikView which I am not able to achieve.

Calculation should basically work like that:

- If status for week is green, show zero (green always resets the count)

- If status for week is red, yellow, and was green previous week, show 1

- If status for week is red, yellow, and was red / yellow previous week show value at previous week + 1

I would be very grateful for any suggestion about how to code this.

BR,

Kuba

[rubenmarin]

Hi Kuba, If the counting column is called Weeks you can use:

If(Flag='Green' or Flag='White', 0, Above(TOTAL [Weeks]+1))

[maxgro]

maybe it helps

- dimensions are yellow

- expression (calcexp) is

if(Flag='Green',0,

     if(match(Flag,'Red','Yellow') and match(Above(TOTAL Flag), 'Green'),1,

     if(match(Flag,'Red','Yellow') and match(Above(TOTAL Flag), 'Red','Yellow'), rangesum(above(total      column(1),1,1))+1,

     ''

)))

[#4]

Hello Ruben,

Thank you for your time spend on this!

Your solution works fine in this specific pivot table. Sorry for not mentioning this before but I was thinking on calculating this using data load. Do you have any idea how can it be done?

BR,

Kuba

[jonathandienst · Accepted solution]

Hi

Create the count in script like:

LOAD

  ...

  Project,

  Week,

  Flag,

  If(Flag = 'Green', 0, If(Previous(Flag)) = 'Green', 1, Peek(WeeksNotInGreen) + 1) As WeeksNotInGreen,

  ...

HTH

Jonathan

[#6]

Thank you Jonathan, that is exactly what i needed