17 Replies Latest reply: Jun 4, 2012 6:18 AM by Søren Andersen

# Using dimension value to sum records outside of dimension

I have a problem that should be quite simple to solve, for someone who knows QlikView a little bit better than I do.

I have the following data:

GroupName, GroupValue, SortOrder, SumGroup

Turnover, 100, 10, 0

Cost of goods sold, -50, 20, 0

Contribution margin 1, 0, 30, 1

Fuelcosts, -25, 40, 0

Contribution margin 2, 0, 50, 1

];

Now I want a chart with GroupName as the dimension, sorted by SortOrder ascending, with the expression:

if(only(SumGroup)=1, 'SUMEXPRESSION', Sum(GroupValue))

Instead of SUMEXPRESSION I want a calculation that sums up all GroupValue's with a SortOrder lower than the current dimension value.

I just can't make an expression that includes the current dimension SortOrder as a variable in the expression.

If I use this expression instead of SUMEXPRESSION:

Sum(Total if(SortOrder<30,GroupValue,0))

Then Contribution margin 1 shows the correct value (because I have harcoded 30, which is Contribution margin 1's SortOrder). But I want to replace 30 with a function of some sort, that returns 30 for contribution margin 1 and 50 for contribution margin 2, dynamically.

If I use Only(SortOrder) as a separat expression in the chart, I get the correct values, but when I substitute 30 in the above expression for Only(SortOrder) I get null.

What am I doing wrong?

• ###### Using dimension value to sum records outside of dimension

As you are sorting by SortOrder ascending, I would guess you could use the Above() function with RangeSum() and RowNo(). I can't remember the exact syntax and cannot acces a QV environment at the moment but try something like:

RangeSum(Above(GroupValue,1,RowNo()))

Look it up as I'm sure I've got it a bit wrong!

Hope this helps,

Jason

• ###### Using dimension value to sum records outside of dimension

Jason,

Changing 1 to 0 in your function helps quite a bit: RangeSum(Above(GroupValue,0,RowNo()))

Now the example adds up the way it should, but unfortunately I can't use this function entirely for the P&L statement I'm trying to build. I have to expand the example to show the problem:

GroupName, GroupValue, SortOrder, SumGroup, FromSortOrder

Turnover, 100, 10, 0, 0

Cost of goods sold, -50, 20, 0, 0

Contribution margin 1, 0, 30, 1, 0

Fuelcosts, -25, 40, 0, 0

Contribution margin 2, 0, 50, 1, 0

Office supplies, -1, 60, 0, 0

Rent, -5, 70, 0, 0

IT expenses, -2, 80, 0, 0

Salaries, -10, 90, 0, 0

Administrative expenses, 0, 100,  1, 60

Net result, 0, 110, 1, 0

];

Here Administrative expenses is a sum of Office supplies, Rent, IT expenses and Salaries, but that requires the expression to be able to weed out all records with a sortorder less than 60. Right now Administrative expenses and Net result is equal (7). But administrative expenses should have been -18.

Can the Rangesum function be extended to do that?

• ###### Using dimension value to sum records outside of dimension

There is an offset parameter in Above().  Maybe do (SortOrder-FromSortOrder)/10 and use that in the offset.

• ###### Using dimension value to sum records outside of dimension

Jason,

I tried that out, but it has it's limitations. If - for some reason - someone wants to exclude "Office supplies" from the P&L then offsetting by (SortOrder-FromSortOrder)/10 will give the wrong number for administrative expenses. Thus it would make the model too static.

Seems to me we have arrived at a paraphrased version of my original problem. How can I use the current dimension value (whether SortOrder or FromSortOrder) to sum up values (GroupValue) outside of the current dimension?

I have looked at many different posts here in the community, and I wonder if it is even possible?

• ###### Using dimension value to sum records outside of dimension

How many group names might there be?

• ###### Using dimension value to sum records outside of dimension

I know of a different way to create the P&L, but that relies on linking the partial sums (like the contribution margin) to the transaction table itself. Although this works, it has the negative effect of enabling drill down on the contribution margin and not making it possible to remove "expense groups" because the transactions are still linked to the contribution margin.

This is what I'm trying to counter, by searching for a solution that will allow the aggregations to be made somewhat independent of the transaction table.

• ###### Using dimension value to sum records outside of dimension

Are the sort orders and FromSortOrders set manually somewhere?

• ###### Re: Using dimension value to sum records outside of dimension

Hi,

I tried a solution according to my understanding, pls see the attached file.

Please note that I have modified the sample data a bit and the solution depends on sort order.

Best Regards,

• ###### Re: Using dimension value to sum records outside of dimension

Hi,

Your solution comes very close, but has the same problem I have experienced with one of the other solutions suggested.

Add GroupName as a ListBox, select all groupnames, deselect "Office Supplies", "Rent" and "Salaries".

Now only -2 remains as "Administrative expenses", but your solutions says -27 because it includes the fuelcosts which is not right, because fuelcost has a lower SortOrder than FromSortOrder of Administrative expenses.

• ###### Re: Using dimension value to sum records outside of dimension

Pls see the attached file, may be this one works.

Best regards,

http://quickdevtips.blogspot.com/

• ###### Re: Using dimension value to sum records outside of dimension

That seems to work exactly like I want it to. Thank you!

• ###### Using dimension value to sum records outside of dimension

Yes they are set in an Excel sheet

• ###### Using dimension value to sum records outside of dimension

Hmm. I have an idea but need to think about it. I'll get back to you when it's taken a bit more shape...

• ###### Re: Using dimension value to sum records outside of dimension

Hi,

Have you considered implementing the PL hierarchy in the data structure?

This will give you the flexibility to exclude certain PL lines (e.g. IT Expenses) and remove the need for a complicated expression.

If you use an indented pivot table, it doesn't look too bad (see attached).

Dilyana

• ###### Using dimension value to sum records outside of dimension

I was also thinking of putting the structure in the script.  Not as a simple heirarchy but as strings of the different sums.  Dilyana's heirarchy idea seems cleaner though - does it help you?

• ###### Using dimension value to sum records outside of dimension

Dilyana,

Thank you for the example, the idea had crossed my mind, but what if I want to drill further down to the specific transaction in the turnover, wouldn't your solution expand all transactions pr. default or be completely collapsed pr. default? Either is not good.

I have marked your answer as helpful because it is an interesting way to do it. I have settled for something else, but I'm still interested in knowing if it is possible to use the dimension value as a way to sum outside of the dimension.

• ###### Using dimension value to sum records outside of dimension

The collapse/expand is not fixed, users can expand only Turnover to see the related transactions.

To answer your question, as far as I know, the dimension value cannot be used in a set analysis and it sometimes can be used in an if statement. I think in your example it's the Total that conflicts with the use of only (dimension)

Thanks,

Dilyana

http://www.capventis.com/