# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

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

Helo,

I have data like this

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.

1 Solution

Accepted Solutions
MVP

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))

12 Replies
MVP

Does this happen only if you select e1, e2? When nothing is selected in F5... would you want to see both the rows?

Contributor III

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

MVP

Try this

IF(\$(val)=0, Sum(F6), IF(Sum(F6)=\$(val), If(RowNo() = 1, \$(val)), 0))

Contributor III

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.

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

];

MVP

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))

Contributor III

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

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?

MVP

I don't think I understand your requirement here... can you elaborate on the steps you are taking to get to this answer?

Contributor III

The new data is

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.

Contributor III

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))

Tags