Using dimension value to sum records outside of dimension

[#1]

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:

LOAD * INLINE [
    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?

[Anonymous]

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

[Jason_Michaelides]

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?

[#4]

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.

[Anonymous]

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/

[#6]

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,

[#7]

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.

[#8 · Accepted solution]

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

Best regards,

http://quickdevtips.blogspot.com/

[#9]

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