topic Re: New Field Requirement in QlikView

New Field Requirement

aveeeeeee7en — Thu, 24 Apr 2014 08:50:11 GMT

Hi All

I want to get the Required field row from Closing MOB Field Values:

Average of Closing MOB in New Field

Month	Closing MOB	Required Field by using CLOSING MOB Field values
Jan	1026	1,026
Feb	1025	1,026
Mar	1027	1,026
Apr	1032	1,028
May	1040	1,030
Jun	1034	1,031
Aug	1015	1,028
Sep	1016	1,026
Nov	1005	1,023
Dec	1002	1,021

Regards

Aviral

Re: New Field Requirement

its_anandrjs — Thu, 24 Apr 2014 08:51:56 GMT

Can you explain by an example to understand. For Apr how comes 1028.

Re: New Field Requirement

Thu, 24 Apr 2014 09:02:20 GMT

Hi,

Like this ?

Load Month,[Closing MOB],[Required Field by using CLOSING MOB Field values]&Temp as [Required Field by using CLOSING MOB Field values];

Load * Inline [
Month, Closing MOB, Required Field by using CLOSING MOB Field values,Temp
Jan, 1026, 1,026
Feb, 1025, 1,026
Mar, 1027, 1,026
Apr, 1032, 1,028
May, 1040, 1,030
Jun, 1034, 1,031
Aug, 1015, 1,028
Sep, 1016, 1,026
Nov, 1005, 1,023
Dec, 1002, 1,021
];

T1:
Load [Required Field by using CLOSING MOB Field values],
Avg([Closing MOB]) as Avg
Resident T
Group By [Required Field by using CLOSING MOB Field values];

Hope it helps you..!

Re: New Field Requirement

aveeeeeee7en — Thu, 24 Apr 2014 09:09:13 GMT

Following is the Calculation:

Month	Closing MOB	Avg Closing MOB	Calculation
Jan	1026	1,026	1026
Feb	1025	1,026	1026+1025=2051/2 =1025.5 =1026
Mar	1027	1,026	1026+1025+1027 =3078/3 =1026
Apr	1032	1,028	1026+1025+1027+1032 =4110/4 =1027.5=1028
May	1040	1,030	1026+1025+1027+1032+1040 =5150/5 =1030
Jun	1034	1,031	1026+1025+1027+1032+1040+1034 =6184/6 =1031
Aug	1015	1,028	1026+1025+1027+1032+1040+1034+1015 =7199/7 =1028
Sep	1016	1,026	and so on
Nov	1005	1,023
Dec	1002	1,021

Re: New Field Requirement

its_anandrjs — Thu, 24 Apr 2014 09:13:32 GMT

Use Peek and previous functions

Re: New Field Requirement

jonathandienst — Thu, 24 Apr 2014 09:35:50 GMT

I am not sure if you want to do this in script, or in a chart/table. If its a chart or table then use:

=RangeAvg(Above([Closing Mob], 0, RowNo()))

HTH

Jonathan

Re: New Field Requirement

jagannalla — Thu, 24 Apr 2014 09:36:28 GMT

Hi,

Take the St.table,

Dimension -> Month

Expression -> Rangeavg (above(sum([Closing MOB]),0,RowNo()))

Hope it may helps you.

Cheers!!!

Jagan

Re: New Field Requirement

aveeeeeee7en — Thu, 24 Apr 2014 09:45:02 GMT

Hi Jonathan Thanks a Ton.

Its working on the Chart Level.

How to do this at Script Level.

Re: New Field Requirement

aveeeeeee7en — Thu, 24 Apr 2014 09:45:36 GMT

Hi Jagan Thanks.

Its Working Perfectlty.

Re: Re: New Field Requirement

jagannalla — Thu, 24 Apr 2014 09:59:27 GMT

Can you make your choice as correct answer/hhelpful answer

Re: New Field Requirement

tresB — Thu, 24 Apr 2014 10:22:20 GMT

Your script solution is here:

Load
Month,
[Closing MOB],
RangeSum([Closing MOB],Peek('CummClosingMOB')) as CummClosingMOB,
RangeSum([Closing MOB],Peek('CummClosingMOB'))/RowNo() as ExpClosingMob

;

Load * Inline [
Month,"Closing MOB"
Jan,1026
Feb,1025
Mar,1027
Apr,1032
May,1040
Jun,1034
Aug,1015
Sep,1016
Nov,1005
Dec,1002

];

Drop field CummClosingMOB;

Re: Re: New Field Requirement

aveeeeeee7en — Thu, 24 Apr 2014 10:39:12 GMT

Hi Tresesco Sir

Your Logic is Perfectly Running.

Your Logics are always Amazing.

One more help I needed from you.

Could you please help me to find the ClosingMoB from Script Level.

I am doing it in Front end which I don't need.

See the Attachment