Avarages based on input value

[#1]

Hi I have scenario,

I need show avarage sales based on input

If input=6 then it has to show last 6 months average

Input=12 it has to show last 12 months average

Please find the attached Excel and qvw files for reference

Regards

Thiru

[sunny_talwar]

What is the expected output when you have input as 6? and what is the output when you have input as 12?

[varshavig12]

you can try something like this :

avg({<Date={"=Addmonths(Date, '-'&vInput "} >} Sales)

where vInput can be your variable

Also, check your date format

[tresesco]

As a first step you have to generate a proper date field in the script (now your date field is string type), like:

Load

          Date(Date#(YourDateString,'YYYY/MMM')) as Date

From <>;

then in the UI, you can try expression something like:

Avg({<Date={'>=$(=MonthStart(Max(Date),-6))<=>=$(=Date(Max(Date)))'}>}Sales)

[#5]

Hi Sunny,

If i give 6 then it need to show last 6 months average

if i give 12 it has to show last 12 months average

Thanks

Thiru

[#6]

Hi,

First you have to convert this Date format to a valid one.

So i used the following simple method to do this.

Mapping:                              //Load a mapping table as shown

Mapping LOAD * INLINE [

    Month, Month#

    Jan, 01

    Feb, 02

    Mar, 03

    Apr, 04

    May, 05

    Jun, 06

    Jul, 07

    Aug, 08

    Sep, 09

    Oct, 10

    Nov, 11

    Dec, 12

];

Sales:                         //Now load the main table and Apply map.

LOAD Date,

     Region,

     Location,

     Sales,

     SubField(Date,'/',1)                  &

                              ApplyMap('Mapping',SubField(Date,'/',2),'Null')        AS Period

FROM

sample_salesdata_V1.xls

(biff, embedded labels, table is Sheet1$);

>>>> For 2016/Sep the Period is 201609, 2016/Aug the Period is 201608 and so on. So its is easy to use them in layout now.

Coming to the layout. Create an Input box and declare variable vNum. Whatever the client is giving as Input is assigned to the variable vNum.

Now create a straight table with Location as Dimension.

Expression to find average based on input value:

Avg({<Period = {">=$(=max(Period)- ($(vNum)-1))"}

>}Sales)

Please let me know for further clarification.

[#7]

Hi

It's not working.Please do the needful

Thiru

[#8]

Hi

It's not working.Please do the needful

Thiru

[tresesco · Accepted solution]

PFA

Avg({<Date={'>=$(=MonthStart(Max(Date),-vInput))<=>=$(=Date(Max(Date)))'}>}Sales)

[#10]

It is working in my application