Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Kuba, If the counting column is called Weeks you can use:
If(Flag='Green' or Flag='White', 0, Above(TOTAL [Weeks]+1))
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,
''
)))
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
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
Thank you Jonathan, that is exactly what i needed