topic Re: Date Range help in QlikView

Date Range help

Wed, 16 Sep 2015 15:48:22 GMT

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.

Re: Date Range help

Wed, 16 Sep 2015 15:57:24 GMT

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?

Re: Date Range help

Thu, 17 Sep 2015 09:33:59 GMT

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

Re: Date Range help

hic — Thu, 17 Sep 2015 13:21:27 GMT

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

Re: Date Range help

Thu, 17 Sep 2015 13:31:14 GMT

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

Re: Date Range help

hic — Thu, 17 Sep 2015 13:35:46 GMT

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

Re: Date Range help

Thu, 17 Sep 2015 15:18:50 GMT

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

Re: Date Range help

hic — Thu, 17 Sep 2015 16:33:20 GMT

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

Re: Date Range help

Fri, 18 Sep 2015 13:59:51 GMT

thanks henric