Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Set analysis not working on dimension level.

I have 1 dimension that returns 4 results.

Sales

COGS

Selling

Gen Admin.

I need to return the margin for only COGS which in turn needs the total of Sales. So I use set analysis:

=sum({<Topincome={"Sales"}>} if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))*-1

When I insert this into the Pivot table it is not returning the correct results. It is still showing 0 for every dimension that does not equal Sales. How would I show the value of sales, but on the dimension of COGS?

Note: This works if I set a variable with a button to sales, but we wan't this to work automaticaly without having to click on something any time the data changes.

1 Solution

Accepted Solutions
swuehl
Not applicable

Set analysis not working on dimension level.

Hi Marc,

coming back to your original approach, have you tried adding the TOTAL QUALIFIER to your sum, like

=sum({<Topincome={"Sales"}>} TOTAL if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))*-1

?

Regards,

Stefan

6 Replies
swuehl
Not applicable

Set analysis not working on dimension level.

Hi Marc,

I don't think the set expression will work in a case like yours.

Have you tried using chart inter record functions like above(), top() and similar?

Regards,

Stefan

Not applicable

Set analysis not working on dimension level.

Yes, so far without the correct results however. There are more dimensions after the first and it gets funny when they are expanded, And I need it to show correctly in the total, but it is showing up for each column, just showing as 0 on the total of the column. I am using:

rangesum(above( total sum({<Topincome={"Sales"}>} if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))*-1 ,0,rowno(total)))

swuehl
Not applicable

Set analysis not working on dimension level.

Marc,

maybe I misunderstood your problem, could you post a small sample here?

Stefan

Not applicable

Set analysis not working on dimension level.

I have a lot of data in this document so here are some screen shots of the table:

The column Sales Set test is using the formula:

rangesum(above( total sum({<Topincome={"Sales"}>} if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))*-1 ,0,rowno(total)))

Untitled.jpg

As you can see it shows the Income value of Sales correctly on each line when it is collapsed, just not on the total line (Which is where I need it). On the second image you can see that as soon as I expand one of the columns the value disappears.

swuehl
Not applicable

Set analysis not working on dimension level.

Hi Marc,

coming back to your original approach, have you tried adding the TOTAL QUALIFIER to your sum, like

=sum({<Topincome={"Sales"}>} TOTAL if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))*-1

?

Regards,

Stefan

Not applicable

Set analysis not working on dimension level.

That seems to be what I needed. Thank you very much. I need to look into adding Total qualifier's I seem to be running into situations that need them a lot.