Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
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
Partner
Partner

Expression as a Variable

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

Expression as a Variable

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.

MVP
MVP

Expression as a Variable

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

Expression as a Variable

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.