topic How to find average value based on Dates? in QlikView

How to find average value based on Dates?

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have question like below,

Name	Date	Value
A	1/1/2017PM	4
B	1/2/2017PM	7
C	1/3/2017PM	4
D	1/4/2017PM	3

from above table I need to find the average value like below table,

Name	Date	Value	Avg.Value
A	1/1/2017PM	4	0.5
B	1/2/2017PM	7	-0.14
C	1/3/2017PM	4	0.545
D	1/4/2017PM	3	-0.33

The above average value is Dummy just for an example

I need output like above table

By using this below formula to find Avg value's

The formula for Avg Value:

=(Today- Previous day)/Previous day*100

In qlik it will be possible to find the Avg.Value dynamically. I am looking for expression wise to get the Avg.Value dynamically.

vengadeshpalani‌ yoganantha321‌

Regards,

Muthukumar

Re: How to find average value based on Dates?

sibusiso90 — Tue, 23 May 2017 08:29:13 GMT

can you show me how you got the first avg and the second one as it is not consistent. normal calculator then I will do it.

Re: How to find average value based on Dates?

sunny_talwar — Tue, 23 May 2017 08:46:08 GMT

I think what you really want is not the average, but the growth number, right? and do you need this in the script or front end object?

Re: How to find average value based on Dates?

vinieme12 — Tue, 23 May 2017 09:08:41 GMT

Yep, agree with Sunny that's basically a change percentage vs previous day that you are asking for; not an average

if you want to do that in a chart it would be something like below

(Sum(Value) - above(Sum(Value)))/ above(Sum(Value))

Re: How to find average value based on Dates?

sibusiso90 — Tue, 23 May 2017 09:15:55 GMT

Even with the growth number it is still not working.

Re: How to find average value based on Dates?

sunny_talwar — Tue, 23 May 2017 11:02:29 GMT

Where are you looking to get this done? Script or front end?

Re: How to find average value based on Dates?

iahmadmca1 — Tue, 23 May 2017 16:48:45 GMT

Hi,

He wants to do this requirement for expression level.

Re: How to find average value based on Dates?

Wed, 24 May 2017 00:47:35 GMT

Thanks for your response Sunny

I need this to be done in Front end Expressions(Straight Table)

Re: How to find average value based on Dates?

Anil_Babu_Samineni — Wed, 24 May 2017 02:48:58 GMT

If you don't mine, Can you please brief one value how you are getting. Let's think our use case is for 0.5. Tell us how you are getting by paper work condition

Re: How to find average value based on Dates?

Wed, 24 May 2017 03:34:52 GMT

No Anil,

the Average that gave is Dummy just for an example

what i actually want is

the Exact Output that i Want:

Name	Date	Value	Avg.Value
A	1/1/2017PM	4	0
B	1/2/2017PM	7	0.75
C	1/3/2017PM	4	-0.42
D	1/4/2017PM	3	-0.25

According to Formula.

(Today-Previous day)/Previous day

Name B:

Today=1/2/2017 value:7

Previous day=1/1/2017 value: 4

Formula:

(7-4)/4=0.75

Name C:

Today=1/3/2017 value:4

Previous day=1/2/2017 value: 7

Formula:

(4-7)/7=-0.42

Name 😧

Today=1/4/2017 value:3

Previous day=1/3/2017 value:4

Formula:

(3-4)/4=-0.25

stalwar1‌

Please help me out of this.

Thanks

Muthukumar

Re: How to find average value based on Dates?

vinieme12 — Wed, 24 May 2017 04:03:01 GMT

Have you tried the expression I posted above?

Re: How to find average value based on Dates?

Anil_Babu_Samineni — Wed, 24 May 2017 04:39:32 GMT

Now Clear,

Try this? and suppress when value is null from Presentation tab of Metric

Num((Sum(Value)-above(TOTAL sum( Value ) ))/above(TOTAL sum( Value ) ),'#,##0.00')

Update -

Round((Sum(Value)-above(TOTAL sum( Value ) ))/above(TOTAL sum( Value ) ),0.001)

Re: How to find average value based on Dates?

Wed, 24 May 2017 05:48:29 GMT

Thanks For your Expression Anil

may i know the Difference between

Num((Sum(Value)-above(TOTAL sum( Value ) ))/above(TOTAL sum( Value ) ),'#,##0.00')

and

Update -

Round((Sum(Value)-above(TOTAL sum( Value ) ))/above(TOTAL sum( Value ) ),0.001)

Re: How to find average value based on Dates?

Anil_Babu_Samineni — Wed, 24 May 2017 05:56:22 GMT

First expression works with Rounded Numbers. but second one gives exact value like you mentioned -0.42

That is how i initiate Round with 0.001

PS - I would request you to close this thread if you found solution as you expecting and please Mark helpful if you found at least any helpful over the thread.

Re: How to find average value based on Dates?

Wed, 24 May 2017 08:24:01 GMT

Thank you So Much, Anil. It is Working fine but for Both the Expression I am Getting same out put. it is not Showing the Exact Values it is Rounding of the values and it is Showing

Re: How to find average value based on Dates?

Anil_Babu_Samineni — Wed, 24 May 2017 08:51:21 GMT

In that second expression, Just use with out Num() as i mentioned last reply