Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to compare in a pivot table a column with another column (and then count a third column)

Hi!

I'm going to show you a tricky situation, hoping somebody has any idea to solve it!

Thank you in advance.


I want to build  Pivot Table with:


Dimension= ID

Expression= for each row I want to count the NULL values of NULLCOLLUMN corresponding to all those values of DATE that are previous than the value of DATE-1workday of that row.

Hope it's clear..!

ID          NULLCOLUMN          DATE     DATE-1workday

1                      a                    11.12.13     10.12.13

1                      -                     15.11.13     14.11.13                  

1                      b                    07.10.14      06.10.14   

2                      c                    23.11.12      21.11.12

2                      d                    07.07.12      06.07.12   

3                      -                     14.01.15      13.01.15   

4                      -                     30.08.14      27.08.14 

4                      -                     15.05.13      14.05.13

ps. i want to clarify that i don't have fixed value to compare with like for example, in a set analysis, but in each row (like in a cycle/loop !?) I want to compare one value of DATE-1workday with all the values of DATE.

2 Replies
robert_mika
Master III
Master III

How your new column (with the count) will look like?

Not applicable
Author

It is supposed to show a column of numbers:

in the first row, for example, the number of times that NULL appears in NULLCOLUMN considering only the rows for which the values of DATE are <= to the value of the first row of DATE-1workday