Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to find average value based on Dates?

Hi,

I have question like below,

  

NameDateValue
A1/1/2017PM4
B1/2/2017PM7
C1/3/2017PM4
D1/4/2017PM

3

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

   

NameDateValueAvg.Value
A1/1/2017PM40.5
B1/2/2017PM7-0.14
C1/3/2017PM40.545
D1/4/2017PM3

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

vengadeshpalaniyoganantha321

Regards,

Muthukumar

1 Solution

Accepted Solutions
Anil_Babu_Samineni

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

15 Replies
sibusiso90
Creator III
Creator III

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

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
Champion III
Champion III

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
Creator III
Creator III

Even with the growth number it is still not working.

sunny_talwar

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

iahmadmca1
Contributor III
Contributor III

Hi,

He wants to do this requirement for expression level.

Not applicable
Author

Thanks for your response Sunny

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

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

No Anil,

the Average that  gave is Dummy just for an example

what i actually want is

the Exact Output that i Want:

NameDateValueAvg.Value
A1/1/2017PM40
B1/2/2017PM70.75
C1/3/2017PM4-0.42
D1/4/2017PM3

-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