Date Range help

[#1]

Hi Experts,

I have the following table.

TableA:

Load Date1,

         Product,

         Quantity,

         Account Name,

         Date2

FROM

QVD\table.qvd

(qvd);

With these, i need to do a pivot table.

My dimensions are Date1, Product, Account Name, Date2.

But Date2 range should be Date1+6 dates.

like below:

Date1   Product   Account Name      Date2

01/09        001            A                         01/09

01/09        002            B                         02/09

......

01/09        006            C                         07/09

How can i do this.

Please help me.

[#2]

not sure what you mean when you say Date2 range should be Date1+6 dates?

there is a qlikView function call AddMonths where you pass in the date and the number of months and it will calulate

AddMonths(startdate, n , [ , mode] )

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

By specifying a mode (0 if omitted) the date is set to either the unmodified day of the specified month (mode=0) or the calculated day as derived from the end of the month (mode=1).

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

addmonths ('2003-01-29',3,0) returns '2003-04-29'

addmonths ('2003-01-29',3,1) returns '2003-04-28'

addmonths ('2003-01-29',1,0) returns '2003-02-28'

addmonths ('2003-01-29',1,1) returns '2003-02-26'

addmonths ('2003-02-28',1,0) returns '2003-03-28'

addmonths ('2003-02-28',1,1) returns '2003-03-31'

are you looking for something like this or is date2 6 days from day 1?

[#3]

Hi,

thanks for the reply.

i m not looking to addmonths.

both date1 & date2 are nearly same here

for eg: the following are the dates

Date1        Date2

01/09         01/09

02/09         02/09

03/09         03/09

.

.

.

.

15/09         15/09

i need to do a pivot table.

My dimensions are Date1, Product, Account Name, Date2.

But Date2 range should be Date1+6 days.

the reason why is,   products are getting transferred from one branch to other

example from branch A to Branch B on one date(Date1) and from branch B to Branch A on another date(Date2)

so i need to form a pivot table like below

Date1   Product   Account Name      Date2

01/09        001            A                         01/09

01/09        002            B                         02/09

......

01/09        006            C                         07/09

Please help me on this

[hic]

Instead of doing some complicated formula in the UI, it is better to prepare it already in the script. Or to be more specific: Calculate the date difference in the script, using e.g.

TableA:

Load Date1, Product, Quantity, Account Name, Date2,

  Date2 - Date1 as DateDifference

FROM QVD\table.qvd (qvd);

With this, you can make selections in the DateDifference field, or use it in Set Analysis, e.g.

  Sum({$<DateDifference={"<=6"}>} Quantity)

That should do the trick

HIC

[#5]

Henric,

thanks for your response.

i dont want to change my script.

i need to do this in the pivot table

is it possible?

how can i do this pls

[hic]

Of course you can do it in the UI, but it will not be fast... If you use

   Sum( If(Date2-Date2<=6,Quantity) )

the expression will be NULL for all other combinations, and the Sum function will return 0. Just make sure that you suppress 0 on the Presentation tab.

HIC

[#7]

Henric,

i dont want to get sum(quantity)

i need to do it in dimension date2

the table like below

Date1   Product   Account Name      Date2

01/09        001            A                         01/09

01/09        002            B                         02/09

......

01/09        006            C                         07/09

please help me

[hic · Accepted solution]

It doesn't matter what you want as expression, you can use this method anyway. You can use anything as expression, also the difference between the dates:

  Only( If(Date2-Date2<=6, Date2-Date2) )

Alternatively, you can make a calculated dimension, e.g. instead of Date2 you could use

  =Aggr( If( Date2-Date2<=6, Date2 , Null()) ,Date1, Date2 )

HIC

[#9]

thanks henric