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

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

Mon, 26 Jan 2015 15:31:39 GMT

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?

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

PrashantSangle — Mon, 26 Jan 2015 15:35:42 GMT

Hi,

Try with Indirect Set analysis

try with p()

Regards,

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

Mon, 26 Jan 2015 15:46:30 GMT

Hi Max,

how i can use this?

Can you explain?

Thank you,

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

agomes1971 — Mon, 26 Jan 2015 17:24:52 GMT

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

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

Mon, 26 Jan 2015 17:43:56 GMT

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?