Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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.

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

12 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

Try this

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


Capture.PNG

Anonymous
Not applicable
Author

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

];

sunny_talwar

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

Anonymous
Not applicable
Author

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?

sunny_talwar

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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