# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save \$400. Learn More
Not applicable

## How to get average of values based on dates?

Hi All,

I have a below table of data. from below data I need to get average

The formula to calculate average:

(Today-Previous day)/Previous day

Example:

TGB name: Bala AMI

Today: 04/09/2017 PM  value: 2

Previous day: 04/08/2017 PM value: 9

based on the formula:

2-9/9=-0.78

 TGB Name Date TGB_Counts Bala_AMI 04/06/2017 PM 4 Bala_AMI 04/07/2017 PM 5 Bala_AMI 04/08/2017 PM 9 Bala_AMI 04/09/2017 PM 2 Metro_AMI 04/06/2017 PM 3 Metro_AMI 04/07/2017 PM 6 Metro_AMI 04/08/2017 PM 8 Metro_AMI 04/09/2017 PM 12 Cheta_AMI 04/06/2017 PM 7 Cheta_AMI 04/07/2017 PM 15 Cheta_AMI 04/08/2017 PM 13 Cheta_AMI 04/09/2017 PM 2

From above table, I am getting the  average like below table

I am using an expression to get Average based on above formula:

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

Note: The average value for each TGB in above table is showing correctly. But if I select particular date with particular TGB name it is showing incorrect data

For example:

TGB Name: Metro_AMI

Selection:

Date:04/08/2017 PM

I am getting a correct average value without selecting the above selection for Bala_AMI

the correct average value is on 04/09/2017PM is -0.78

But if I select the above date: it is showing incorrect data or it is not showing any data on the selected date

is there any way to fix this: please any one help me out of this I am stuck on this issue for many days

Please modify my expression or provide me a new expression

please find the attached qvw and data for your reference

Thanks,

Muthu

4 Replies
Valued Contributor

## Re: How to get average of values based on dates?

Hi, Muthu!

I think the best way is make some script calculation with Previous() finction.

Load your data as Table1, then use Previous() function in Resident table, and make calculate of average depend on your order. Like that:

tab1:

Load

[TGB Name],Date,TGB_Counts

from Path;

NoConcatenate

tab2:

Load

*,

(TGB_Counts - Previous(TGB_Counts))/Previous(TGB_Counts) as avg_calc

Resident tab1

Order by [TGB Name],Date;

After that you will be able to select anything you want and the values of average will be the same.

Highlighted
MVP

## Re: How to get average of values based on dates?

May be try this expression

=Num((Sum(TGB_Counts)/Above(TOTAL Sum({<Date>}TGB_Counts)) - 1) * Avg(1),'#,##0.00')

or this

=Num((Sum(TGB_Counts)/Above(Sum({<Date>}TGB_Counts)) - 1) * Avg(1),'#,##0.00')

Not applicable

## Re: How to get average of values based on dates?

Thank you So Much Sunny It is working Fine.

Can I use the Same Expression for Line Chart for Same Data it like a trending Line Chart

or I want to use Different Expression. and Also can you Explain me the above Expression.

MVP

## Re: How to get average of values based on dates?

I think it should work in line chart unless you have found it otherwise?