Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

how to compare single Date Column value with all values of another Date Column

hello every one,

I try to explain my problem, for privacy i cannot display my sample data, but my question is this:

i have something like this table:

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

I want to build  Pivot Table that has ID as dimension and one expression that counts the null values of NULLCOLUMN for all those DATE values <= than every value of "DATE-1workday".

Have someone any ideas about the expression script?

4 Replies

Re: how to compare single Date Column value with all values of another Date Column

Hi,

Try with Indirect Set analysis

try with p()

Regards,

Regards,
Prashant Sangle
Not applicable

Re: how to compare single Date Column value with all values of another Date Column

Hi Max,

how i can use this?

Can you explain?

Thank you,

agomes1971
Valued Contributor

Re: how to compare single Date Column value with all values of another Date Column

Hi,

You can only use p() and e() function in a set analysis set modifier.

From the Help file:

Set Modifiers with implicit field value definitions

In the above examples, all field values have been explicitly defined or defined through searches. There is however an additional way to define a set of field values by the use of a nested set definition.

In such cases, the element functions P() and E() must be used, representing the element set of possible values and the excluded values of a field, respectively. Inside the brackets, it is possible to specify one set expression and one field, e.g. P({1} Customer). These functions cannot be used in other expressions:

Examples:

sum( {$<Customer = P({1<Product={‘Shoe’}>} Customer)>} Sales )

returns the sales for current selection, but only those customers that ever have bought the product ‘Shoe’. The element function P( ) here returns a list of possible customers; those that are implied by the selection ‘Shoe’ in the field Product.

sum( {$<Customer = P({1<Product={‘Shoe’}>})>} Sales )

same as above. If the field in the element function is omitted, the function will return the possible values of the field specified in the outer assignment.

sum( {$<Customer = P({1<Product={‘Shoe’}>} Supplier)>} Sales )

returns the sales for current selection, but only those customers that ever have supplied the product ‘Shoe’. The element function P( ) here returns a list of possible suppliers; those that are implied by the selection ‘Shoe’ in the field Product. The list of suppliers is then used as a selection in the field Customer.

sum( {$<Customer = E({1<Product={‘Shoe’}>})>} Sales )

returns the sales for current selection, but only those customers that never bought the product ‘Shoe’. The element function E( ) here returns the list of excluded customers; those that are excluded by the selection ‘Shoe’ in the field Product.

Regards

André Gomes

Not applicable

Re: how to compare single Date Column value with all values of another Date Column

thanks Andrè,

but i don't have a singlie fix value that i can compare my variable with

but an ntire set of values,

all the values of DATE-1daywork.
Suggestions?

Community Browser