Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

SUM TOTAL by expression values

Hello,

is it possible to sum up values by the output of another expression using the TOTAL qualifier? Example:

Column C is an calculated expression based on column B. I would like to aggregate the amount (column D) by the output of column C. The desired output is displayed in column E. In Qlikview, I am trying to achieve this result using the function TOTAL:

However, I receive the following error: "Total specifier must be a valid field name" .

Is there another way to solve this problem? Please find attached the qvw containing the example.

Please also note that this is an simplified example and in reality the currency calculation is very complex containing several IF-statements so it is not possible to use the whole calculation within the total function (total<original expression>).

Many thanks,

Paul

1 Solution

Accepted Solutions
juraj_misina
Luminary Alumni
Luminary Alumni

Hi Paul,

First, I would assume that TOTAL qualified (like set analysis) is not evaluated on row level, but on chart level, so I guess you're not able to make it dynamic using conditions. Second, it expects field name as a input. Your variable returns currency code (which is not a field name). I remember trying to use table column in TOTAL without success and I rewrote the expression using creating separate Sum(TOTAL) for each scenario, summing them up by RangeSum() (I might have used Aggr() as well, don't rememnber, it was some time ago).

So, my recomendation is to calculate an extra field in script, which would contain proper currency for this aggregation, and you don't need to do this kind of gymnastics.

Hope this helps.

Juraj

View solution in original post

7 Replies
sunny_talwar

Try this

If(Match(Customer,'A','B','C'), Sum(TOTAL <Currency> {<Customer = {'A', 'B', 'C'}>}Amount))

Anil_Babu_Samineni

Try this?

If(Match(Customer,'A','B','C'), Sum(TOTAL <Currency> {<Customer = {'A', 'B', 'C'}>}Amount))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable
Author

Thank you!

My currency calculation is more complex, so I have updated the example. Outcome should be as follows (green column):

juraj_misina
Luminary Alumni
Luminary Alumni

Hi Paul,

First, I would assume that TOTAL qualified (like set analysis) is not evaluated on row level, but on chart level, so I guess you're not able to make it dynamic using conditions. Second, it expects field name as a input. Your variable returns currency code (which is not a field name). I remember trying to use table column in TOTAL without success and I rewrote the expression using creating separate Sum(TOTAL) for each scenario, summing them up by RangeSum() (I might have used Aggr() as well, don't rememnber, it was some time ago).

So, my recomendation is to calculate an extra field in script, which would contain proper currency for this aggregation, and you don't need to do this kind of gymnastics.

Hope this helps.

Juraj

sunny_talwar

I agree with juraj.misina‌, if this is something you can do in the script (creating currency_cal in the script), then you will save yourself from a very complicated expression (not even sure this is possible to do yet).

Anonymous
Not applicable
Author

Hi Juray,

thanks for your input - I have been afraid it's to complicated for an expression. However, it's a little odd that it's not possible to use calculated values for aggregation, since the result is available on row level.

Cheers,

Paul

somacdc
Contributor III
Contributor III

Hi,

I have a chart with customers and I want to know the distinct number of customers they have gross amount.

For the selected applicable_date I tried following formula

Sum(TOTAL<customer>
{<
APPLICABLE_DATE -={"'-'"},
NUM_APPLICABLE_DATE = {'>=$(=Floor(Num(Account_Date_From)))<=$(=Floor(Num(Account_Date_To)))'}
>}
GROSS_AMOUNT)

Please note that Account_Date_From and Account_Date_To are the variables

Anyway, here is the chart

clipboard_image_1.png

Here's an example of one of customer data. according to this Gross Amount is 270.

Customer NameApplication DateGross Amount
NEWFUNDABC27/02/203250
NEWFUNDABC27/02/203220
NEWFUNDABC27/02/203240
NEWFUNDABC27/02/203230
NEWFUNDABC04/03/203220
NEWFUNDABC04/03/2032100
NEWFUNDABC04/03/203210

 

Thanks in advance for your help.