GDR calculation

[#1]

Hello ALl,

I need to perform a calculation, to make it more easy i report below 2 fields:

RETURNTYPE

MONTH

i need to calcutate how many units we have with condiction (returntype='multi')/((month)+(month-1)+(month-2)) and i need it for all month

what can i do? what can be the structure of the script?

thank you very much

Massimiliano

[el_aprendiz111]

Hi,

You can send a sample of your data

[Anil_Babu_Samineni]

Are you expecting something like this?

Front end:

Count({<returntype = 'multi'>} units) / (Count({<month = {'$(=Max(month))'}>}units) + Count({<month = {'$(=AddMonths(Max(month),-1))'}>}units) + Count({<month = {'$(=AddMonths(Max(month),-2))'}>}units))

[Digvijay_Singh]

If you can share sample data and expected output, it will help to build what is needed. Could be many ways, see if something like below, it won't work as it is, just a thought..

Source:

Load Dim1,

         Date,

         Month(Date) as Month,

         returntype,

         Units

From SourceData;

Monthdata:

Load

        Dim1,

        Month,

         if( returntype='Multi',Count(Units)) as Multi_UnitCount;

  Resident Source

Group By Dim1, Month

Order by Dim1,Return Type, Month

Left join (Monthdata)

Quarterdata:

Load

  Dim1,

  Month,

  Rangesum(Multi_UnitCount,Previous(Multi_UnitCount),Previous(Previous(Multi_UnitCount) as 3Monthscount

Resident Monthdata

[#5]

Hello,

I will try to explain better the situation, what i need to do is the following table:

And the file that im loading was converted in this script:

[#6]

Thank you for the answer, i add a comment to explain better the situation.

[#7]

Thank you for the answer, i add a comment to explain better the situation.

[Digvijay_Singh]

Something like this -

Source:

Load * inline [

DIM, Year, Month, ReturnType

DIM1, 2014, Jan, MULTI

DIM1, 2014, Jan, NOTMULTI

DIM1, 2014, Feb, MULTI

DIM1, 2014, Feb, NOTMULTI

DIM1, 2014, Mar, MULTI

DIM1, 2014, Mar, NOTMULTI

DIM1, 2014, Apr, MULTI

DIM1, 2014, Apr, NOTMULTI

DIM1, 2014, May, MULTI

DIM1, 2014, May, NOTMULTI

DIM1, 2014, Jun, MULTI

DIM1, 2014, Jun, NOTMULTI

DIM1, 2014, Jul, MULTI

DIM1, 2014, Jul, MULTI

DIM1, 2014, Aug, NOTMULTI

DIM1, 2014, Aug, MULTI

DIM1, 2014, Sep, NOTMULTI

DIM1, 2014, Sep, MULTI

DIM1, 2014, Oct, NOTMULTI

DIM1, 2014, Oct, MULTI ];

Final:

Load *,

  if(Rowno()>2,

  MultiCount/Rangesum(MonthCount,Previous(MonthCount), Previous(Previous(MonthCount)))) as GDR;

Load DIM,

  Year,

  Month,

  Sum(if(ReturnType='MULTI',1)) as MultiCount,

  Count(ReturnType) as MonthCount

Resident Source

Group By DIM, Year,Month

;

Drop table Source;

[Digvijay_Singh]