topic Dollar Sign Expansion and Sum in QlikView

Dollar Sign Expansion and Sum

chris_johnson — Thu, 14 Apr 2011 13:15:34 GMT

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

Dollar Sign Expansion and Sum

IAMDV — Thu, 14 Apr 2011 13:41:51 GMT

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

Dollar Sign Expansion and Sum

chris_johnson — Thu, 14 Apr 2011 14:01:54 GMT

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

Dollar Sign Expansion and Sum

IAMDV — Thu, 14 Apr 2011 15:33:01 GMT

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.

Dollar Sign Expansion and Sum

nathanfurby — Thu, 14 Apr 2011 16:09:19 GMT

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.

Dollar Sign Expansion and Sum

chris_johnson — Thu, 14 Apr 2011 16:12:06 GMT

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

Thanks for your efforts.

Chris

Dollar Sign Expansion and Sum

IAMDV — Thu, 14 Apr 2011 16:21:44 GMT

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.

Dollar Sign Expansion and Sum

nathanfurby — Thu, 14 Apr 2011 17:28:07 GMT

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.

Dollar Sign Expansion and Sum

IAMDV — Thu, 14 Apr 2011 17:48:06 GMT

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!

Dollar Sign Expansion and Sum

chris_johnson — Thu, 14 Apr 2011 19:41:22 GMT

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

Dollar Sign Expansion and Sum

chris_johnson — Thu, 14 Apr 2011 19:56:02 GMT

Nathan, just had a look at your solution. I like it!

Thanks again for everyone's help

Chris

Dollar Sign Expansion and Sum

IAMDV — Fri, 15 Apr 2011 09:16:42 GMT

Chris - Thanks for sharing your idea. Yes, thanks for unmarking my post as an answer and marking Nathan's recent post as an answer.

Cheers!

Dollar Sign Expansion and Sum

nathanfurby — Fri, 15 Apr 2011 09:20:22 GMT

No worries guys. I'd also keep DV's answer marked - it is correct for what you were trying to do and that post might help other people understand what's going on.

Re: Dollar Sign Expansion and Sum

Thu, 29 Mar 2012 08:12:24 GMT

@Chris: I found use of your solution, but if you want to use LineNo as a your table is not filled in LineNo order the concat-solution will be messed up

Expressions:

load * inline [

Line Description, Expression, LineNo

UK Sales, "Sum({<Region={'UK'}>}"Transaction Amount")", 1

US Sales, "Sum({<Region={'US'}>}"Transaction Amount")", 3

SE Sales, "Sum({<Region={'SE'}>}"Transaction Amount")", 2

];

It can be saved by including LineNo as a sort_weight into the concat() statement like this

=concat({1}Expression,',',LineNo)

It will force QlikView to do the concat in the numeric LineNo-order starting with the lowest number.