topic Re: Pivot table with grouping in QlikView

Pivot table with grouping

Wed, 19 Nov 2014 09:20:48 GMT

Hi all,

hope you can help me on this issue:

my raw data contains 3 data sets:

id	date1	date2	class	amount
1	01.01.2012	05.01.2012	a	1
2	02.01.2012	05.01.2012	b	2
3	03.01.2012	05.01.2012	b	3

As you can see

set 1 is overdue 4 days (05.01.2012 - 01.01.2012 = 4 days)
set 2 is overdue 3 days (05.01.2012 - 01.01.2012 = 3 days)
set 3 is overdue 2 days (05.01.2012 - 01.01.2012 = 2 days).

Now I would like to present the sum of amounts in a pivot table grouped by class like this:

class	total	overdue <= 2 days	overdue >= 4 days
a	1	0	1
b	5	3	0

My pivot table in QlikView contains the dimension "class" and the following 3 expressions:

sum(amount) labeled "total"
if (date2-date1 <= 2, sum(amount), 0) labeled "overdue <= 2 days"
if (date2-date1 >= 4, sum(amount), 0) labeled "overdue >= 4 days"

With these expressions my pivot table does not look like expected but it looks like this:

class	total	overdue <= 2 days	overdue >= 4 days
a	1	0	1
b	5	0	0

Do you know what I did wrong?

Thanks in advance for your help.

Best regards

Dan

P.S. See also my QVW attached.

Re: Pivot table with grouping

SunilChauhan — Wed, 19 Nov 2014 09:32:24 GMT

see attched file

use date2-date1 as difference in script

hope this helps

Re: Pivot table with grouping

sujeetsingh — Wed, 19 Nov 2014 09:32:44 GMT

Your pivot table is 100% correct.

When you write if(date2-date2<=2,sum(Amount),0)

Here the date2-date1 is grouped on the dimension you are using that is Class

For Class b the Date2-date1 has two values as 2 and 3 , now for the whole class it is added as 2+3=5 which is not <=2 hence it represents 0

Re: Pivot table with grouping

Wed, 19 Nov 2014 09:39:26 GMT

My problem is that the users should have the possibility to change the value for date2 with a calender object in the GUI. Then the pivot table should be re-calculated.

That is why, I think I could not calculate the difference in the load script.

Re: Pivot table with grouping

Wed, 19 Nov 2014 09:47:33 GMT

Ok, I see. Is it possible to re-write my expression, that only the amount of data set 3 is taken into account for class b (not the whole sum)?

Re: Pivot table with grouping

Wed, 19 Nov 2014 09:51:35 GMT

Perhaps there is a way to re-calculate the difference when the user changes date2 in the calender object (e.g. with a trigger or something else)?

Re: Re: Pivot table with grouping

Wed, 19 Nov 2014 16:26:48 GMT

Please find attached my test.qvw using a calendar object for date2.