topic Re: Pivot table before/after function with two dimensions. in QlikView

Pivot table before/after function with two dimensions.

jeevays7 — Thu, 09 Feb 2017 11:58:50 GMT

Hi all,

I have a pivot table with data as below,

Date	25-Nov-2016	25-Nov-2016	25-Nov-2016	02-Dec-2016	02-Dec-2016	02-Dec-2016
Name	MGT FEES	PIF	PERF FEES	MGT FEES	PIF	PERF FEES
MENA	10.00	20.00	30.00	5.00	10.00	20.00
Raj	1.00	3.00	4.00	5.00	3.00	4.00
Tamil	4.00	6.00	6.00	1.00	2.00	2.00

Now i want the result like below table,

In below table i take the (25-NOV-2016) MGT FEES and subtract with next day(02-Dec-2016) MGT FEES value.

so like that all date values subtracted with previousday values with coressponding Names.

Date	25-Nov-2016	25-Nov-2016	25-Nov-2016	02-Dec-2016	02-Dec-2016	02-Dec-2016
Name	MGT FEES	PIF	PERF FEES	MGT FEES	PIF	PERF FEES
MENA	5.00	10.00	10.00	5.00	10.00	20.00
Raj	-4.00	0.00	0.00	5.00	3.00	4.00
Tamil	3.00	4.00	4.00	1.00	2.00	2.00

How to achieve this in pivot table?

Re: Pivot table before/after function with two dimensions.

swuehl — Thu, 09 Feb 2017 13:10:53 GMT

MGT FEES is an expression, right?

Date is your pivoted dimension and Name dimension shows values like MENA, Raj, Tamil, right?

Have you tried something like

= [YourExpression for MGT FEES] - After( [YourExpression for MGT FEES] )

Or use Before(), not sure what you want to calculate here.

Replace [YourExpression for MGT FEES] with your expression used, e.g. Sum(Sales)

Re: Pivot table before/after function with two dimensions.

sunny_talwar — Thu, 09 Feb 2017 13:26:07 GMT

I think be better to use RangeSum() so that the next day value doesn't turn null here:

RangeSum([YourExpression for MGT FEES], -After( [YourExpression for MGT FEES]))

Re: Pivot table before/after function with two dimensions.

jeevays7 — Thu, 09 Feb 2017 14:51:44 GMT

Hi Sunny and Stefan,

here MGT FEES,PIF and PERF FEES also a dimension(Desc) values.

	Date	25-Nov-2016
Name	Desc	MGT FEE
MENA		10.00
RAJ		1.00

Re: Pivot table before/after function with two dimensions.

sunny_talwar — Thu, 09 Feb 2017 14:52:40 GMT

What is your expression here?

Re: Pivot table before/after function with two dimensions.

sunny_talwar — Thu, 09 Feb 2017 14:54:34 GMT

I think this should still work for you:

RangeSum(ExpressionHere, -After(ExpressionHere))

Replace ExpressionHere with whatever your expression is

Re: Pivot table before/after function with two dimensions.

jeevays7 — Thu, 09 Feb 2017 14:55:28 GMT

just sum(values) is my expression

Re: Pivot table before/after function with two dimensions.

sunny_talwar — Thu, 09 Feb 2017 14:57:27 GMT

Can you try this:

RangeSum(Sum(values), -After(Sum(values)))

Re: Pivot table before/after function with two dimensions.

jeevays7 — Thu, 09 Feb 2017 14:57:42 GMT

Hi sunny,

Name,Date,Desc is my dimensions and sum(Value) is my expression.

Name dimension is being left side and Date, Desc dimension should be top of the pivot table

Re: Pivot table before/after function with two dimensions.

jeevays7 — Thu, 09 Feb 2017 15:03:04 GMT

The pivot table looking like this without design.

	Date		25-Nov-2016			02-Dec-2016
Name	Desc	MGT FEES	PIF	PERF FEES	MGT FEES	PIF	PERF FEES
MENA		10.00	20.00	30.00	5.00	10.00	20.00
Raj		1.00	3.00	4.00	5.00	3.00	4.00
Tamil		4.00	6.00	6.00	1.00	2.00	2.00

Re: Pivot table before/after function with two dimensions.

sunny_talwar — Thu, 09 Feb 2017 15:04:38 GMT

Try this:

=Aggr(RangeSum(Sum(Value), -Below(Sum(Value))), Name, Desc, Date)

Re: Pivot table before/after function with two dimensions.

swuehl — Thu, 09 Feb 2017 15:09:28 GMT

Try

=Only(

Aggr(

Sum(Value) - Below( Sum(Value) )

,NAME, DESC, DATE)

)

If your DATE field values are not in chronological load order, use

The sortable Aggr function is finally here!

Re: Pivot table before/after function with two dimensions.

jeevays7 — Thu, 09 Feb 2017 15:12:33 GMT

Hi sunny,

the above yours exp is not working

Re: Pivot table before/after function with two dimensions.

sunny_talwar — Thu, 09 Feb 2017 15:15:14 GMT

It's not working in your actual scenario or the output from the sample is not what you want? As Stefan pointed, for this to work you need the date field to be sorted in ascending order via the load script.... If it isn't then you can use Sorting through Aggr(), but for that you would need to have at least QV12. Do you have QV12? If yes, then you can try this:

=Aggr(RangeSum(Sum(Value), -Below(Sum(Value))), Name, Desc, (Date, (NUMERIC)))

Re: Pivot table before/after function with two dimensions.

jeevays7 — Thu, 09 Feb 2017 15:15:22 GMT

Hi Stefan,

Your expression is not working for me.

Re: Pivot table before/after function with two dimensions.

jeevays7 — Fri, 10 Feb 2017 11:40:08 GMT

Thank you sunny and stefan for all your reply's.

Now it's working.