topic Re: Aggr & avg in QlikView

Aggr & avg

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

Hi there!

Need your help so much with issue: I need to calculate Average by Dates with formula: 1-fact/fact2.

I've managed with Text Object, but I don't know what to do with table:

For text object I've used formula:

Num(
                 Avg(
                                Aggr(1-Sum(Fact1)/Sum(Fact2),Date)
                                ),
                 '# ##0,000%')

But it doesn't fit for table (second expression in table).

I tried to add field "Channel" to Aggr expression, but still doesn't work properly (first expression in table).

Re: Aggr & avg

vinieme12 — Fri, 31 Mar 2017 13:03:55 GMT

try

Num(

Avg( TOTAL

Aggr(1-Sum(Fact1)/Sum(Fact2),Date)

'# ##0,000%')

Num(

Avg(

Aggr(NODISTINCT (1-Sum(Fact1)/Sum(Fact2)),Date)

'# ##0,000%')

Re: Aggr & avg

aisolomatin — Fri, 31 Mar 2017 13:21:57 GMT

Vineeth, thanx for your reply, but it still doesn't fit (same values for C1 and C2).

Re: Aggr & avg

sunny_talwar — Fri, 31 Mar 2017 13:28:03 GMT

This one should, I think

Num(

Avg( TOTAL

Aggr(1-Sum(Fact1)/Sum(Fact2),Date)

'# ##0,000%')

Re: Aggr & avg

vinieme12 — Fri, 31 Mar 2017 13:29:57 GMT

try below

Num(

Avg( TOTAL

Aggr(1-Sum(Fact1)/Sum(Fact2),Date)

'# ##0,000%')

Re: Aggr & avg

aisolomatin — Fri, 31 Mar 2017 13:35:03 GMT

Hi,Sunny! Nope, still doesn't fit:

Channal	Date	Fact1	Fact2
C1	01.01.2017	10	100
C1	02.01.2017	8	70
C2	01.01.2017	3	10
C2	02.01.2017	5	9

Re: Aggr & avg

aisolomatin — Fri, 31 Mar 2017 13:36:27 GMT

Nope, doesn't work (see previous answer, please).

Re: Aggr & avg

sunny_talwar — Fri, 31 Mar 2017 13:39:36 GMT

Why add <Channel>?

This worked for me

Avg(TOTAL Aggr(1-Sum(Fact1)/Sum(Fact2), Date))

Re: Aggr & avg

sunny_talwar — Fri, 31 Mar 2017 13:42:37 GMT

I guess what is the expected output here?

Re: Aggr & avg

aisolomatin — Fri, 31 Mar 2017 14:01:19 GMT

Sunny,

I expect to see that table:

Channal	Value
Total	85,863%
C1	88,182%
C2	83,544%

Re: Aggr & avg

sunny_talwar — Fri, 31 Mar 2017 14:06:16 GMT

Date is not a dimension? or you want this?

Re: Aggr & avg

aisolomatin — Fri, 31 Mar 2017 14:08:37 GMT

Yes, I don't want Date as dimension.

Re: Aggr & avg

sunny_talwar — Fri, 31 Mar 2017 14:11:51 GMT

Can you explain that why C1 gets the percentage that is associated with Date 01.01.2017 and C2 gets the percentage associated with Date 02.01.2017. Both have Fact1 and Fact2 for both the dates?

Re: Aggr & avg

aisolomatin — Fri, 31 Mar 2017 15:19:56 GMT

Sunny,

my falt, you are right. Correct values:

Channel	Avg
C1	89,286%
C2	57,222%
Total	85,863%

Calculation for C1:

((1-10/100)+(1-8/70))/2=89,286%

С2:

((1-3/10)+(1-5/9))/2=57,222%

Total:

((1-(10+3)/(100+10)-(1-(8+5)/(70+9))/2 = 85,863%

Re: Aggr & avg

sunny_talwar — Fri, 31 Mar 2017 15:25:03 GMT

Here you are

If(Dimensionality() = 0, Avg(Aggr(1-Sum(Fact1)/Sum(Fact2), Date)), Avg(Aggr(1-Sum(Fact1)/Sum(Fact2), Date, Channal)))

Re: Aggr & avg

aisolomatin — Fri, 31 Mar 2017 15:33:03 GMT

Magic! Thank you!

Can you pls give a few words about using Dimensionality()?

Re: Aggr & avg

sunny_talwar — Fri, 31 Mar 2017 15:34:40 GMT

Read here

How to use - Dimensionality()

and here

The second dimension... or how to use secondarydimensionality()

Re: Aggr & avg

ascioneanna — Thu, 21 Sep 2017 11:20:45 GMT

Hi can someone help me with this question ?

avg(aggr())

Anna