topic Re: GDR calculation in QlikView

GDR calculation

Tue, 30 May 2017 12:54:05 GMT

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

el_aprendiz111 — Tue, 30 May 2017 19:46:50 GMT

Hi,

You can send a sample of your data

Re: GDR calculation

Anil_Babu_Samineni — Wed, 31 May 2017 02:22:57 GMT

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

Digvijay_Singh — Wed, 31 May 2017 04:30:56 GMT

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

Re: GDR calculation

Wed, 31 May 2017 08:07:25 GMT

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:

Re: GDR calculation

Wed, 31 May 2017 08:08:04 GMT

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

Re: GDR calculation

Wed, 31 May 2017 08:08:09 GMT

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

Re: GDR calculation

Digvijay_Singh — Wed, 31 May 2017 14:47:19 GMT

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, 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;

Re: GDR calculation

Digvijay_Singh — Wed, 31 May 2017 14:56:43 GMT