topic 3 Months Average in Straight Table ? in QlikView

3 Months Average in Straight Table ?

rajuamet — Wed, 25 Nov 2020 16:16:04 GMT

Hi Techies,

I need to find average of 3 months in a straight table. Here Month_Yr is dimension. I have measure field day wise from source table.

Example:

For Month_Yr "Mar-2015", calculate average for values from 01-Jan-2015 to 31-Mar-2015.

For Month_Yr "Apr-2015", calculate average for values from 01-Feb-2015 to 30-Apr-2015 and so on.

Here I have attached Test files for your reference.

Re: 3 Months Average in Straight Table ?

vinieme12 — Sat, 30 Jul 2016 06:50:33 GMT

Do you need this in your load script of straight table?

Re: 3 Months Average in Straight Table ?

rajuamet — Sat, 30 Jul 2016 07:39:02 GMT

Hi Vineeth,

I need this in straight table without making changes in script.

Re: 3 Months Average in Straight Table ?

Kushal_Chawda — Sat, 30 Jul 2016 07:45:39 GMT

try this expression

=RangeAvg( above(avg(Measure),0,3))

Re: 3 Months Average in Straight Table ?

rajuamet — Sat, 30 Jul 2016 08:36:15 GMT

Hi Kushal,

For example,

Month1 => 10, 20, 30, 40

Month2 => 50, 60, 70

Month3 => 90, 10, 20, 30

Your expression does this:

Month1=> (10+20+30+40)/4 => (100/4) => 25

Month2=> (50+60+70)/3 => (180/3) => 60

Month3=> (90+10+20+30)/4 => (150/4) => 37.5

then, (25+60+37.5)/3 => (122.5/3) = 40.8333...

But actually it should take average for 3 months with day wise values. Therefore it should do,

(10+20+30+40+50+60+70+90+10+20+30) / 11 = 39.0909...

Here 39.0909 is the required result.

How can I achieve this.........

Re: 3 Months Average in Straight Table ?

Kushal_Chawda — Sat, 30 Jul 2016 09:14:27 GMT

I done the logic as per your result needed excel file which is matching with your needed output

Re: 3 Months Average in Straight Table ?

Kushal_Chawda — Sat, 30 Jul 2016 09:22:55 GMT

what is the expected output you need for Mar - 2015?

Re: 3 Months Average in Straight Table ?

rajuamet — Sat, 30 Jul 2016 09:26:01 GMT

Hi Kushal,

Required Result

Result from your expression

Values after decimal point does not match... Because average has to be applied as per the above example.

Re: 3 Months Average in Straight Table ?

Anil_Babu_Samineni — Sat, 30 Jul 2016 12:15:58 GMT

You want to show last months data while selection or Constant?

Re: 3 Months Average in Straight Table ?

sunny_talwar — Sat, 30 Jul 2016 13:27:55 GMT

Script:

Sample:

LOAD Date,

Floor(MonthEnd(Date)) - MonthStart(Date, -2) + 1 as Days,

Measure,

Month(Date)&'-'&Year(Date) as Month_Yr

FROM

[Sample Data (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

Where Days field will create a field which will help us know how many days do we need to go back for each month (which are not static). So months will have 91, 90, or 89 days

Once you get this under your belt, the next step is to use the following formula, but this will only work in QV12 (because sorting in Aggr is only available in QV12 or Qlik Sense The sortable Aggr function is finally here!)

=Aggr(If(Date = Floor(MonthEnd(Date)), RangeAvg(Above(Avg(Measure), 0, Days))), (Date, (NUMERIC)))

If you don't have QV12, you will need to sort the date in the script, which might be difficult if you can bring your master calendar before anything else. If you can do this, then you can use this:

=Aggr(If(Date = Floor(MonthEnd(Date)), RangeAvg(Above(Avg(Measure), 0, Days))), Date)

Regular expression works here because your data was well sorted in the Excel file, which might not be true for your real data.

HTH

Best,

Sunny

Re: 3 Months Average in Straight Table ?

rajuamet — Sun, 31 Jul 2016 08:14:12 GMT

Hi Anil,

It should be constant.

Re: 3 Months Average in Straight Table ?

rajuamet — Sun, 31 Jul 2016 08:15:21 GMT

Awesome Sunny! This is what I want. Thanks .

Re: 3 Months Average in Straight Table ?

sunny_talwar — Sun, 31 Jul 2016 08:18:24 GMT

Great

Re: 3 Months Average in Straight Table ?

vinieme12 — Sun, 31 Jul 2016 08:54:05 GMT

Without making changes to your load script you can use expression as below

rangesum(above(sum(Measure),0,3))

rangesum(above(Count(DateIS),0,3))

Re: 3 Months Average in Straight Table ?

rajuamet — Sun, 31 Jul 2016 10:14:13 GMT

Extraordinary Vineeth ... Perfect...