Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

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?

1 Solution

Accepted Solutions
Not applicable
Author

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

Best regards,

http://quickdevtips.blogspot.com/

View solution in original post

17 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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:

LOAD * INLINE [

    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?

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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?

Jason_Michaelides
Luminary Alumni
Luminary Alumni

How many group names might there be?

Not applicable
Author

Anywhere between about 20 and about 50 pr. P&L.

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.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Are the sort orders and FromSortOrders set manually somewhere?

Not applicable
Author

Yes they are set in an Excel sheet

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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