Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

Tags (1)
8 Replies
el_aprendiz111
Valued Contributor

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


Life is so rich, and we need to respect to the life !!!
Digvijay_Singh
Honored Contributor III

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:

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

Not applicable

Re: GDR calculation

Hello,

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

1.PNG

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

2.PNG

Not applicable

Re: GDR calculation

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

Not applicable

Re: GDR calculation

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

Digvijay_Singh
Honored Contributor III

Re: GDR calculation

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
Honored Contributor III

Re: GDR calculation

Capture.PNG

Community Browser