Pivot table - show first dimension value based on row level condition

[Anonymous]

Helo,

I have data like this

LOAD * INLINE [

    F1, F2, F3, F4, F5, F6

    a1, b1, c1, d1, e1, -2

    a1, b1, c1, d1, e2, -2

    a1, b1, c1, d1, e3, -3

    a2, b2, c2, d2, e2, -24

    a3, b3, c3, d3, e1, -10

];

On the sheet, I used a listbox to select F5s that I want to see. I also used an inputbox to specify the sum(F6) values that I want to see.

If i select F5 = e1, e2 and give -2 in the inputbox, the pivot table display below rows.

   a1, b1, c1, d1, e1, -2

   a1, b1, c1, d1, e2, -2

Question - Is it possible to display just one of that row? That is, if F1, F2, F3, F4 are same and if sum(F6) is also same then just display one of the F5, maybe the first one.

Attached the qvw.

[sunny_talwar]

Nice

[Anonymous]

I hate to continue this thread after marking the correct answer. Since we already set the context I am continuing but pls let me know if you need a new thread.

The requirement changed. The value i entered before in the input box are in an island table now.

LOAD * INLINE [

SF7, SF8

S1, -5

S2, -24

];

The pivot table now has SF7 field and sum of F6 is shown if it matches SF8.

Pls look at the attached file.

The "Table 2" pivot table shows the records correctly with more than one F5.

To reduce it to min F5, in "Table 3", I applied your minstring logic like this

IF(Sum(Total <F1, F5> F6)=SF8,

If(MinString(TOTAL <F1, F2, F3, F4, SF7> Aggr(IF(Sum(Total <F1, F5> F6)=SF8, F5), F1, F2, F3, F4, F5)) = F5,

Sum(F6))

, 0

)

I was thinking the aggr will consider SF7/SF8 of that row only but looks like it includes both values.

How do we make the aggr use the SF7/SF8 corresponding to the row?

[sunny_talwar]

May be this

IF(Sum(Total <F1, F5> F6)=SF8,

If(MinString(TOTAL <F1, F2, F3, F4, SF7> Aggr(IF(Sum(Total <F1, F5> F6)=SF8, F5), F1, F2, F3, F4, F5, SF7)) = F5,

Sum(F6))

, 0

)