Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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)))
Marc,
maybe I misunderstood your problem, could you post a small sample here?
Stefan
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.
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
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.