topic Re: pivot with range date diff in QlikView

pivot with range date diff

Thu, 17 Jul 2014 10:05:54 GMT

hi i have a big big problem..

i have a table for example

NUMORDINE

DATE1

DATE2

DATE3

i want create a pivot with range date diff

range diff ->	1	2	3	4
count distinct NUMORD where DATE1-DATE2 ->	5	0	2	5
count distinct NUMORD where DATE2-DATE3 ->	6	1	3	6

but is possible ?

for only row was simple,

i used for dimension

if (

Aggr(DISTINCT DATE1,NUMORDINE)>0 and Aggr(DISTINCT DATE2,NUMORDINE)>0

(

Aggr(DISTINCT Interval(

date(date#(DATE2,'YYYYMMDD'),'YYYYMMDD')

- date(date#(DATE1,'YYYYMMDD'),'YYYYMMDD'),'d'),

NUMORDINE)

)

and for expression

count(DISTINCT NUMORDINE)

but now ? is impossible !!

Re: pivot with range date diff

hic — Thu, 17 Jul 2014 10:48:57 GMT

I would already in the script define fields with the differences:

date#(DATE2,'YYYYMMDD') - date#(DATE1,'YYYYMMDD') as Difference12

date#(DATE3,'YYYYMMDD') - date#(DATE2,'YYYYMMDD') as Difference23

Then you can use these fields as dimensions. No Aggr() needed.

HIC

Re: pivot with range date diff

Thu, 17 Jul 2014 10:52:23 GMT

yes but i cant use in script..

beacuse i have a slidebar with range date value and my boss don't want reload data any change on sidebar !!!

Re: pivot with range date diff

hic — Thu, 17 Jul 2014 10:58:52 GMT

Then try

Aggr(

If( Only(Date#(DATE2,'YYYYMMDD')-Date#(DATE1,'YYYYMMDD')) > 0,

Only(Date#(DATE2,'YYYYMMDD')-Date#(DATE1,'YYYYMMDD'))

NUMORDINE)

But I don't like using interpretation functions in the UI... They should be in the script.

HIC

Re: pivot with range date diff

Thu, 17 Jul 2014 12:26:33 GMT

tx for reply but this don't resolve my problem..

i need more than 1 diff !

i need Difference12 and Difference24 in one table pivot wtih range number as dimension !!