topic Re: Help with aggr dimension in QlikView

Help with aggr dimension

educastri83 — Wed, 25 Nov 2020 16:16:04 GMT

Hi! community.

759/5000

I have the following problem that I would like to solve

I have a KPi that measures the bills that have had a discount greater than 80% and that also the total of the invoice is equal to 1 dollar. In a text object I can represent it in the following way:

= Count (DISTINCT Aggr (If ((sum ({<CHARGE_TYPE_DESC = {'Credit'}>} CHARGE_NET_AMOUNT)
+
sum ({<CHARGE_TYPE_DESC = {'Discount'}>} CHARGE_NET_AMOUNT) )
/
sum ({<CHARGE_TYPE_DESC = {'Debit'}>} CHARGE_NET_AMOUNT)> = 0.8

AND SUM (CHARGE_NET_AMOUNT)> = 1, INVOICE_KEY), INVOICE_KEY))

The above works well in a text object, but now I need to create a dimension where the previous condition is fulfilled and I can make graphics, among others.

I think it is using AGGR in the dimension but the truth is not my strong

thanks for your help

CC:

stalwar1‌

Re: Help with aggr dimension

sunny_talwar — Tue, 30 Oct 2018 22:27:58 GMT

What exactly are you hoping to see in your dimension? A single value or bunch of values?

Re: Help with aggr dimension

educastri83 — Wed, 31 Oct 2018 00:03:25 GMT

In this case a single value,

but I would appreciate if you guide me for when it comes to a bunch of values.

Thanks

Re: Help with aggr dimension

sunny_talwar — Wed, 31 Oct 2018 11:09:15 GMT

For single value the total qualifier should work...

=Count (TOTAL DISTINCT Aggr(If((Sum({<CHARGE_TYPE_DESC = {'Credit'}>} CHARGE_NET_AMOUNT) + Sum({<CHARGE_TYPE_DESC = {'Discount'}>} CHARGE_NET_AMOUNT))

/
Sum({<CHARGE_TYPE_DESC = {'Debit'}>} CHARGE_NET_AMOUNT) >= 0.8 and Sum(CHARGE_NET_AMOUNT) >= 1, INVOICE_KEY), INVOICE_KEY))

But for multiple values, which field or fields would you want the count on?

Re: Help with aggr dimension

educastri83 — Wed, 31 Oct 2018 21:34:56 GMT

Thanks Sunny

The operation

sum ({<CHARGE_TYPE_DESC = {'Credit'}>} CHARGE_NET_AMOUNT)

sum ({<CHARGE_TYPE_DESC = {'Discount'}>} CHARGE_NET_AMOUNT) )

sum ({<CHARGE_TYPE_DESC = {'Debit'}>} CHARGE_NET_AMOUNT)

it can be too between:

>0.01 - < 0.20

> 0.20 and <0.40

> 0.40 and <0.80

> 0.80 (its okey, already )

= 100

Sum(CHARGE_NET_AMOUNT) >= 1, INVOICE_KEY don't change it stays the same

Re: Help with aggr dimension

educastri83 — Sun, 04 Nov 2018 02:02:07 GMT

Hi Sunny,

I have this other example:

I got a bar char that contains 7 expressions, each one for a brunch of values. The Bar chart dont have a dimenssion and really,,, i don't like it.. I would like to do better

The detail for each bar it is:

1 Expression: 70 - 75

=Count(DISTINCT Aggr(If(Sum(

CHARGE_NET_AMOUNT) <= 74.9 AND Sum(CHARGE_NET_AMOUNT) >= 70 , INVOICE_KEY), INVOICE_KEY))

2. Expression: 60-69

=Count(DISTINCT Aggr(If(Sum(CHARGE_NET_AMOUNT) <= 69.9 AND Sum(CHARGE_NET_AMOUNT) >= 60 , INVOICE_KEY), INVOICE_KEY))

3. Expression: 50-59

=Count(DISTINCT Aggr(If(Sum( CHARGE_NET_AMOUNT) <= 59.9 AND Sum(CHARGE_NET_AMOUNT) >= 50 , INVOICE_KEY), INVOICE_KEY))

..And so on until reach the last expression.

The Bar chart i've created looks like:

I wanted to create an adequate dimension to simplify brunch of values expressions and can counting the numbers of Invoice..

from now thank you very much if you can help me stalwar1‌