Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Sum Aggr Sum with multiple set analysis query

Hi all,

I have an expression where I need to use multiple set analysis criteria. A simplified version is below

Sum({<Set Analysis Criteria 1>} Aggr (Sum {<Set Analysis Criteria 1>}  Sales), Division))

+

Sum({<Set Analysis Criteria 2>} Aggr (Sum {<Set Analysis Criteria 2>}  Sales), Division))


I was hoping and expecting that I could combine them into a simpler version, e.g.

Sum({<Set Analysis Criteria 1>+<Set Analysis Criteria 2>}

Aggr (

Sum {<Set Analysis Criteria 1>+<Set Analysis Criteria 2>}  Sales), Division))


but this isnt giving me the same answer.


Does it not work like this? Or is it not working in mine becuse of the set analysis criteria I am using?


I would be grateful of any help.


Also, as a side question, do I necessary need the same Set analysis criteria after both "sums" in a sum-aggr-sum expression?

Tags (2)
1 Solution

Accepted Solutions
Highlighted

Re: Sum Aggr Sum with multiple set analysis query

Another way to do this would be (not that it would be any better)

Sum(Aggr(

     RangeSum(Sum({<Transaction_Type={'C'}>}Value),

     -Sum({<Transaction_Type={'C'}, Customer_Name={'Void'}>}Value),

     -Sum({<Transaction_Type={'C'}, Customer_Type={2}, Customer_Name={'*'}-{'Void'}>}Value))

, CustomerID, Week))

View solution in original post

8 Replies
Highlighted

Re: Sum Aggr Sum with multiple set analysis query

Not always are they going to work... if the set analysis overlap, the sum from the two expressions can overlap and the two expressions can give you very different numbers.

For your second question, it usually makes sense to add the same set analysis from your inner aggregation to your outer aggregation

Highlighted

Re: Sum Aggr Sum with multiple set analysis query

Please post full metric to check

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Re: Sum Aggr Sum with multiple set analysis query

My real expression is something more like

Sum ({<Transaction_Type={'C'}>}

Aggr (Sum {<Transaction_Type={'C'}>}Value), CustomerID, Week))

-

Sum ({<Transaction_Type={'C'},  Customer_Name={'Void'}>}

Aggr (Sum {<Transaction_Type={'C'}, Customer_Name={'Void'}>}Value), CustomerID, Week))

-

Sum ({<Transaction_Type={'C'}, Customer_Type={2}, Customer_Name={'*'}-{'Void'}>}

Aggr (Sum {<Transaction_Type={'C'}, Customer_Type={2}, Customer_Name={'*'}-{'Void'}>}Value), CustomerID, Week))

So, its a case of

Show me the sum off all C transactions

Less all C transactions for Voids

Less all C transactions for Type 2 customers except Voids (as they have been excluded above)

If someone can show me a better / cleaner way of writing that, I would appreciate it

Highlighted

Re: Sum Aggr Sum with multiple set analysis query

Another way to do this would be (not that it would be any better)

Sum(Aggr(

     RangeSum(Sum({<Transaction_Type={'C'}>}Value),

     -Sum({<Transaction_Type={'C'}, Customer_Name={'Void'}>}Value),

     -Sum({<Transaction_Type={'C'}, Customer_Type={2}, Customer_Name={'*'}-{'Void'}>}Value))

, CustomerID, Week))

View solution in original post

Highlighted
Master II
Master II

Re: Sum Aggr Sum with multiple set analysis query

in which object you are using the expression ? as you are using aggr(sum() it must be a text box.

Karl Hart wrote:

So, its a case of

Show me the sum off all C transactions

Less all C transactions for Voids

Less all C transactions for Type 2 customers except Voids (as they have been excluded above)

If someone can show me a better / cleaner way of writing that, I would appreciate it

when you said less all you meant diffrence or exclusion ? if exclusion and i understood it correctly than you dont need a complex expression.

Learning never stops.
Highlighted
Specialist III
Specialist III

Re: Sum Aggr Sum with multiple set analysis query

HI,

try this

Sum(

     Aggr(

                 Sum ( {<Transaction_Type={'C'} ,  Customer_Type-={2} , Customer_Name-={'Void'}>} Value)

            , CustomerID, Week

             )

        )

Highlighted
Creator
Creator

Re: Sum Aggr Sum with multiple set analysis query

Thanks, that has worked and reduced the calculation time - which is what I was trying to achieve with this

Highlighted
Creator
Creator

Re: Sum Aggr Sum with multiple set analysis query

Thank you, I did think about this after I posted the question, but there is another criteria which I didnt include which means there is some overlapping and so this wouldnt work in my case