Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
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 NameDateTGB_Counts
Bala_AMI04/06/2017 PM4
Bala_AMI04/07/2017 PM5
Bala_AMI04/08/2017 PM9
Bala_AMI04/09/2017 PM2
Metro_AMI04/06/2017 PM3
Metro_AMI04/07/2017 PM6
Metro_AMI04/08/2017 PM8
Metro_AMI04/09/2017 PM12
Cheta_AMI04/06/2017 PM7
Cheta_AMI04/07/2017 PM15
Cheta_AMI04/08/2017 PM13
Cheta_AMI04/09/2017 PM2

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

Screenshot_6.png

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

Screenshot_7.png

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

Screenshot_8.png

Screenshot_9.png

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
Sergey_Shuklin
Specialist
Specialist

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.

sunny_talwar

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
Author

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.

sunny_talwar

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