8 Replies Latest reply: May 31, 2017 10:56 AM by Digvijay Singh

# GDR calculation

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

• ###### Re: GDR calculation

Hi,

You can send a sample of your data

• ###### Re: GDR calculation

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

• ###### Re: GDR calculation

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

• ###### Re: GDR calculation

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:

Date,

Month(Date) as Month,

returntype,

Units

From SourceData;

Monthdata:

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:

Dim1,

Month,

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

Resident Monthdata

• ###### Re: GDR calculation

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

• ###### Re: GDR calculation

Hello,

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

• ###### Re: GDR calculation

Something like this -

Source:

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:

if(Rowno()>2,

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

Year,

Month,

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

Count(ReturnType) as MonthCount

Resident Source

Group By DIM, Year,Month

;

Drop table Source;