topic Re: How to get average of values based on dates? in QlikView

How to get average of values based on dates?

Wed, 25 Nov 2020 16:16:04 GMT

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

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

Sergey_Shuklin — Thu, 01 Jun 2017 11:36:06 GMT

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.

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

sunny_talwar — Thu, 01 Jun 2017 11:52:10 GMT

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

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

Thu, 01 Jun 2017 12:35:11 GMT

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.

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

sunny_talwar — Thu, 01 Jun 2017 12:38:55 GMT

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