Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?
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
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
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
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
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
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
thanks henric