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: 
Not applicable

Expression as a Variable

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;

error loading image

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

4 Replies
syed_muzammil
Partner - Creator II
Partner - Creator II

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.



Not applicable
Author

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
Champion III
Champion III

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.

Not applicable
Author

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.