Solved: Re: How to find average value based on Dates? - Qlik Community

Report Inappropriate Content · ‎2017-05-23

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

Anil_Babu_Samineni · ‎2017-05-24

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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

sibusiso90 · ‎2017-05-23

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.

sunny_talwar · ‎2017-05-23

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?

vinieme12 · ‎2017-05-23

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

sibusiso90 · ‎2017-05-23

Even with the growth number it is still not working.

sunny_talwar · ‎2017-05-23

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

iahmadmca1 · ‎2017-05-23

Hi,

He wants to do this requirement for expression level.

Report Inappropriate Content · ‎2017-05-23

Thanks for your response Sunny

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

Anil_Babu_Samineni · ‎2017-05-23

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Report Inappropriate Content · ‎2017-05-23

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