topic Re: Syntax issues on getting total for a line in QlikView

Syntax issues on getting total for a line

Dayna — Tue, 22 Sep 2015 10:14:12 GMT

Hello All,

I've attached my application that I'm having issues with, I want to perform a percent calculation that uses the first expression (Volume) and divide by the total volume - which is the value I currently have as a calculated dimension.

However, syntax is getting the better of me and I'm struggling to get the same value to apply across all the 'band names' (the dimension used in the pivot table at the top).

I'm hoping someone can help me, an example would be the first row; cust code = YVBFU105, the total volume is 1,958.40 and I'd like that value to be displayed for each band name in my second expression. Then what I'll do is divide the value from the band name by the total volume (1,958.40) to give me my percentage. But my expression only seems to put the total in one of the band name values - not all of them.

Your help is always greatly appreciated!! Many thanks in advance.

Kind Regards,

Dayna

Re: Syntax issues on getting total for a line

sasiparupudi1 — Tue, 22 Sep 2015 10:53:11 GMT

This?

=num(sum(Aggr(sum(aggr(sum({$<[AR - BandName] = {'Current'}>}[AR - total]/[AR - Ex Rate]),[AR - Cust Bill])),[AR - BandName]))

sum(aggr(aggr(sum({$<[AR - BandName] = {'1-30 Days', '31-60 Days', '61-90 Days', '91 Days - Year', 'Year +'} >} [AR - total]/[AR - Ex Rate]),[AR - Cust Bill])

,[AR - BandName]))

,'#,##0.00')

Re: Syntax issues on getting total for a line

cjohnson — Tue, 22 Sep 2015 17:18:29 GMT

Hi Dayna,

Try using the "NODISTINCT" qualifier for your aggr function as shown below;

I believe that get's you your desired result.

Let me know if that helps!

Thanks,

Camile

Re: Syntax issues on getting total for a line

Dayna — Wed, 23 Sep 2015 07:52:24 GMT

Camile,

That's perfect, thank you! How does the NODISTINCT work in this example? Is it something on the lines of that the aggregation function will ignore the different values (i.e. band name) that it would normally be segregated by?

Kind Regards,

Dayna

Re: Syntax issues on getting total for a line

cjohnson — Wed, 23 Sep 2015 18:07:10 GMT

If omitted, By default the aggr function uses DISTINCT as its qualifier. If we look at the definition of the aggr function we see the following:

aggr ( [distinct | nodistinct ] [set_expression] expression {, dimension} )

If the expression argument is preceded by the nodistinct qualifier,

each combination of dimension values may generate more than one

return value, depending on underlying data structure. If the expression

argument is preceded by the distinct qualifier or if no qualifier

is used at all, each combination of dimension values will generate

only one return value.

In your application that is exactly what is occurring - the NODISTINCT qualifier allows you to generate more than one return value (only one was being returned before because the qualifier was omitted.

Re: Syntax issues on getting total for a line

Dayna — Thu, 24 Sep 2015 07:59:04 GMT

Thanks Camile! Must remember for next time..!