topic Re: SUM TOTAL by expression values in QlikView

SUM TOTAL by expression values

Anonymous — Fri, 12 Jan 2018 15:06:16 GMT

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

Re: SUM TOTAL by expression values

sunny_talwar — Fri, 12 Jan 2018 15:09:42 GMT

Try this

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

Re: SUM TOTAL by expression values

Anil_Babu_Samineni — Fri, 12 Jan 2018 15:11:09 GMT

Try this?

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

Re: SUM TOTAL by expression values

Anonymous — Mon, 15 Jan 2018 10:43:00 GMT

Thank you!

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

Re: SUM TOTAL by expression values

juraj_misina — Mon, 15 Jan 2018 11:48:24 GMT

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

Re: SUM TOTAL by expression values

sunny_talwar — Mon, 15 Jan 2018 11:56:03 GMT

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

Re: SUM TOTAL by expression values

Anonymous — Mon, 15 Jan 2018 14:23:53 GMT

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

Re: SUM TOTAL by expression values

somacdc — Fri, 18 Oct 2019 07:20:37 GMT

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

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

Customer Name	Application Date	Gross Amount
NEWFUNDABC	27/02/2032	50
NEWFUNDABC	27/02/2032	20
NEWFUNDABC	27/02/2032	40
NEWFUNDABC	27/02/2032	30
NEWFUNDABC	04/03/2032	20
NEWFUNDABC	04/03/2032	100
NEWFUNDABC	04/03/2032	10

Thanks in advance for your help.