topic Re: Customizing Pivot table partial Sum in QlikView

Customizing Pivot table partial Sum

lionking15 — Thu, 19 Jan 2017 15:12:25 GMT

Hi Guys

Sno	Name	Sales
Q1	Jan	10
	Feb	20
	Mar	30
	Total	60
Q2	Apr	40
	May	50
	Jun	60
	Total	150

I want above pivot table as

Final Output

Sno	Name	Sales
Q1	Jan	10
	Feb	20
	Mar	30
	Total	30
Q2	Apr	40
	May	50
	jun	60
	Total	60

Total should be equal to max month of particular quarter.

Re: Customizing Pivot table partial Sum

sunny_talwar — Thu, 19 Jan 2017 15:17:29 GMT

Try this expression:

=If(Dimensionality() = 1, FirstSortedValue(Aggr(Sum(Sales), Sno, Name), -Aggr(Name, Sno, Name)), Sum(Sales))

Re: Customizing Pivot table partial Sum

vishsaggi — Thu, 19 Jan 2017 15:25:09 GMT

May be try this in your expression:

= IF(Dimensionality() = 1, Max(Sales), Sum(Sales))

Re: Customizing Pivot table partial Sum

sunny_talwar — Thu, 19 Jan 2017 15:28:55 GMT

What if the Max sales was associated with the month of Jan? I think the sample data might make us think that Max(Sales) will work, but in reality if we wish to pick the sales associated with Max month, we need to use FirstSortedValue here

Re: Customizing Pivot table partial Sum

vishsaggi — Thu, 19 Jan 2017 15:32:42 GMT

I got it now. You are right. We need firstsorted value.

Re: Customizing Pivot table partial Sum

vinieme12 — Thu, 19 Jan 2017 15:43:50 GMT

or this

=If(Dimensionality() = 1,Sum({<Name = {"=Name = AGGR(Max(TOTAL <Sno> Name),Sno,Name)"}>}Sales) , Sum(Sales))