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

[chris_johnson]

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

Thanks again for everyone's help

Chris

[IAMDV]

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!

[nathanfurby]

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.

[#5]

@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.