Expression as a Variable

[#1]

Dear All,

I have a pivot table in which i have used the following expression,

If (Match(Group,'OTH'),If([Position]='LONG',Sum(YTD_CV)))/ 1000

So i get this result;

I get the result i need in the red box.

Now, however, I need to use the total - 12,958 as a variable so that it can be used in other tables.

I tried using the following;

Sum(Aggr(If(Match(Group,'OTH'),If([Position]='LONG',Sum(YTD_CV)))))/1000 but it displays a null value.

Can someone help here?

PS. I cannot post a sample since I open the file in server mode.

King Regards,

M.K.S

[syed_muzammil]

Hi,

You can instead use

if(Group='OTH' and Position='LONG',Sum(Total<Group> YTD_CV ))/1000

It will give you the Sum(YTD_CV) aggregated on Group.

[#3]

Hi Syed,

Thanks for your reply, if i use the formula you suggested it still doesnt work, i think its because the Position in the expression is caluclated using other calculated expressions from the pivot table/. I cannot write the whole formula since it is too long.

I just need to use the total of the column as a variable because i need to use it for calculation in another table/chart.

[johnw]

I think you have two basic mistakes in your expression. First, correct, you can't use [Position] if it is just a named expression in a chart. You have to recalculate it in your new expression. For simplicity, I'm going to assume your expression is If(Rand()>.5,'LONG','SHORT'), which obviously it isn't. Substitute your own expression of course. Second, when you aggr(), you need to tell it what dimensions you want to aggregate by, in this case, Group and Currency. So I think you want something like this:

Sum(Aggr(If(Match(Group,'OTH'),If(If(Rand()>.5,'LONG','SHORT')='LONG',Sum(YTD_CV))),Group,Currency))/1000

Now, this particular example could be further simplified, but I wanted to directly use your existing expressions. Feel free to simplify from there.

If the expression for short or long is too big, consider making IT a variable, and using it in both places.

[#5]

Thanks John for your reply. I havent had a chance to try out your suggestions but will post a reply with the results once i've tested it out.

Many Thanks.