Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
raju_insights
Partner
Partner

3 Months Average in Straight Table ?

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.

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

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)

Capture.PNG

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

View solution in original post

14 Replies
vinieme12
Champion II
Champion II

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

raju_insights
Partner
Partner
Author

Hi Vineeth,

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

Kushal_Chawda

try this expression

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

raju_insights
Partner
Partner
Author

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

Kushal_Chawda

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

Kushal_Chawda

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

raju_insights
Partner
Partner
Author

Hi Kushal,

Required Result

Result Needed.JPG

Result from your expression

pastedImage_0.png

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

Anil_Babu_Samineni

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

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

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

Capture.PNG

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)

Capture.PNG

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

View solution in original post