Set analysis not working on dimension level.

[#1]

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.

[swuehl]

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

[#3]

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]

Marc,

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

Stefan

[#5]

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

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 · Accepted solution]

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

[#7]

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.