Dollar Sign Expansion and Sum

[chris_johnson]

Hi,

I'm trying to create an inline table storing various line desciptions and calculations.

At the moment I have:

Expressions:
load * inline [
"Line Description", Expression
Work In Progress Movement, "Sum ({<"Prior-Year Entry"={'*'},TransType={'Actual'},IncExpCode= {'400R'}, GLAcTable.Income_Balance={0}, Type={1}, LineDesc={'Work In Progress Movement'}>}Amount)"

Invoiced Sales, "Sum({<"Prior-Year Entry"={'*'},TransType={'Actual'},IncExpCode= {'400R'},GLAcTable.Income_Balance={0}, Type={1}>}Amount)-Sum ({<"Prior-Year Entry"={'*'},TransType={'Actual'},IncExpCode= {'400R'}, GLAcTable.Income_Balance={0}, Type={1}, LineDesc={'Work In Progress Movement'}>}Amount)"
];

So I create 2 rows (which look ok when I view them in a table box) each with a description and formula in.

The problem I am having is when I put the Month-Year (from a Calendar table) in the dimensions and use $(=Expression) as an expression in a chart I only ever get figures if I select one of the Line Descriptions. So, in other words, the value will only calculate if I have exactly one LIne Description/Expression selected. If both Line Descriptions are available then I have 'No data to display' in the chart.

Does anyone have any ideas why this is? Or how I can use multiple rows with multiple formulas to calculate correctly?

Thanks,

Chris

[IAMDV]

Chris - Do you have a sample QV document? I can try this one...

[chris_johnson]

Hi,

Thanks for the offer. I've managed to knock up a quick qvw with some random data I've made up to explain what I mean.

Chris

[IAMDV]

Hi Chris,

Thanks for posting the sample QV document. I did look at the script & chart expression and everything seems to be right. I can't figure out the issue, I am very sorry if I had wasted your time. Although I had found a post from the forum... not sure if its the same problem.

http://community.qlik.com/forums/t/19497.aspx

I am very keen to see a solution for this issue.

Thanks again!

Good luck.

[nathanfurby]

The explanation is from John in the post that DV just found:

http://community.qlik.com/forums/t/19497.aspx

The dollar sign expansion works outside the dimensions of the table. So will only ever work when only one is selected. I suggest you find another way to go about the problem - even if it means more hard-coding in the table expression.

[chris_johnson]

Fair enough, I'll try to re-work my solution.

Thanks for your efforts.

Chris

[IAMDV]

Chris - I have very similar requirement very soon. So please can you tell me the effecient way of handling this issue. I mean what would you do to solve this issue with least efforts?

Thanks in anticipation.

[nathanfurby · Accepted solution]

OK - so just had an additional thought about this as it was bugging me from earlier.

Attached is my solution which may work for you. It uses a variable which concatenates the expressions. I added a LineNo value in the Load to identify for the pick in the expression.

Not sure it makes sense, don't know why you'd need it, but seems to solve your particular problem. Let me know if you have any questions.

[IAMDV]

NathanFurby - I really love this now. I am taking a bow for this one. Very very smart solution and this is exactly what I need. I owe you for this one...

Thanks a ton again!

I actually don't deserve points for this one... how can we change it?

Cheers!

[chris_johnson]

Hi both,

Have been away from the internet for a while but thought I'd have another look locally. Had a fiddle with my example and got it to work in a slightly different way. Had to create seperate tables for each expression and then do a check in the straight table to see which expression to use.

I've uploaded it so by all means have a look and tell me your opinion.

Nathan - thanks for putting your effort in too, I'll have a look now and see how you've done it.

DV - I can unmark this as an answer I think and mark Nathan's as the answer if that's ok with you all?

Thanks again to you both,

Chris