Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be give this a shot
IF($(val)=0, Sum(F6), IF(Sum(F6)=$(val), If(MinString(TOTAL <F1, F2, F3, F4> Aggr(IF(Sum(F6)=$(val), F5), F1, F2, F3, F4, F5)) = F5, $(val)), 0))
Does this happen only if you select e1, e2? When nothing is selected in F5... would you want to see both the rows?
No always. Whatever F5 I select, if F1, F2, F3, F4 are same and if sum(F6) is also same then display one of the F5
Try this
IF($(val)=0, Sum(F6), IF(Sum(F6)=$(val), If(RowNo() = 1, $(val)), 0))
RowNo=1 does not work if the first row is zero. With below data and F5=e0, e1, e2 selected and val= -2, pivot is not showing any data.
LOAD * INLINE [
F1, F2, F3, F4, F5, F6
a1, b1, c1, d1, e0, 0
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
];
May be give this a shot
IF($(val)=0, Sum(F6), IF(Sum(F6)=$(val), If(MinString(TOTAL <F1, F2, F3, F4> Aggr(IF(Sum(F6)=$(val), F5), F1, F2, F3, F4, F5)) = F5, $(val)), 0))
This works for the requirement I mentioned. Thanks.
But turns out the $(val) is sum grouped by F1, F5 only not with all 5 dimensions. So with below data, if I select F5=e0, e1, e2 and give val = -5, the pivot should display
a1, b1, c1, d1, e1, -2
a1, b2, c2, d2, e1, -3
LOAD * INLINE [ !
F1, F2, F3, F4, F5, F6
a1, b1, c1, d1, e0, 0
a1, b1, c1, d1, e1, -2
a1, b2, c2, d2, e1, -3
a1, b1, c1, d1, e2, -2
a1, b2, c2, d2, e2, -3
a1, b1, c1, d1, e3, -3
a2, b2, c2, d2, e2, -24
a3, b3, c3, d3, e1, -10
];
I tried changing your expression to this but it is not displaying any record
IF($(val)=0, Sum(F6), IF(Sum(F6)=$(val), If(MinString(TOTAL <F1, F2, F3, F4> Aggr(IF(Sum(Total <F1, F5> F6)=$(val), F5), F1, F2, F3, F4, F5)) = F5, $(val)), 0))
Any idea?
I don't think I understand your requirement here... can you elaborate on the steps you are taking to get to this answer?
The new data is
LOAD * INLINE [
F1, F2, F3, F4, F5, F6
a1, b1, c1, d1, e0, 0
a1, b1, c1, d1, e1, -2
a1, b2, c2, d2, e1, -3
a1, b1, c1, d1, e2, -2
a1, b2, c2, d2, e2, -3
a1, b1, c1, d1, e3, -3
a2, b2, c2, d2, e2, -24
a3, b3, c3, d3, e1, -10
];
Earlier the value i entered in the inputbox was sum of F6 grouped by F1, F2, F3, F4, F5. Now that is sum of F6 grouped by F1 and F5 only.
So with previous sum requirement, if I give -2, and select F5=e0, e1, e2 I got theses rows
a1, b1, c1, d1, e1, -2
a1, b1, c1, d1, e2, -2
With your spl logic it reduced to
a1, b1, c1, d1, e1, -2
With new sum requirement, if i select F5=e0, e1, e2 and give -5 I would get these rows. Here sum of F6 by F1 and F5 is -5.
a1, b1, c1, d1, e1, -2
a1, b2, c2, d2, e1, -3
a1, b1, c1, d1, e2, -2
a1, b2, c2, d2, e2, -3
With spl logic it should get reduced to
a1, b1, c1, d1, e1, -2
a1, b2, c2, d2, e1, -3
For this, in the spl logic, instead of comparing sum(F6)=$(val) I gave Sum(Total <F1, F5> F6)=$(val) but it doesn't seem to work.
IF($(val)=0, Sum(F6), IF(Sum(F6)=$(val), If(MinString(TOTAL <F1, F2, F3, F4> Aggr(IF(Sum(Total <F1, F5> F6)=$(val), F5), F1, F2, F3, F4, F5)) = F5, $(val)), 0))
The sample i attached in the previous reply has the new data and the logic
Hope I made myself clear. Let me know.
It works with this logic. Thanks.
IF($(val)=0, Sum(F6), IF(Sum(Total <F1, F5> F6)=$(val), If(MinString(TOTAL <F1, F2, F3, F4> Aggr(IF(Sum(Total <F1, F5> F6)=$(val), F5), F1, F2, F3, F4, F5)) = F5, Sum(F6)), 0))