topic Re: Avarages based on input value in QlikView

Avarages based on input value

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Avarages based on input value

sunny_talwar — Wed, 02 Nov 2016 11:33:04 GMT

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

Re: Avarages based on input value

varshavig12 — Wed, 02 Nov 2016 11:54:49 GMT

you can try something like this :

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

where vInput can be your variable

Also, check your date format

Re: Avarages based on input value

tresB — Wed, 02 Nov 2016 12:17:54 GMT

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)

Re: Avarages based on input value

Wed, 02 Nov 2016 12:27:31 GMT

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

Re: Avarages based on input value

Thu, 03 Nov 2016 07:19:02 GMT

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.

Re: Avarages based on input value

Thu, 03 Nov 2016 07:54:05 GMT

It's not working.Please do the needful

Thiru

Re: Avarages based on input value

Thu, 03 Nov 2016 07:54:16 GMT

It's not working.Please do the needful

Thiru

Re: Avarages based on input value

tresB — Thu, 03 Nov 2016 08:32:15 GMT

PFA

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

Re: Avarages based on input value

Thu, 03 Nov 2016 09:29:23 GMT

It is working in my application

Re: Avarages based on input value

Thu, 03 Nov 2016 09:52:17 GMT

It's working

=AVG({<Date1={'>=$(=AddMonths(Date(V_DB_Max_date),-(Vinput-1)))<=$(=Date(V_DB_Max_date))'}>} Sales)

V_DB_MAX_DATE is the max date varaiable

Thanks

Thiru