Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

add values of a column for the start of each month

Hi, how should the script be like if I want to add up the balance(refer below) for 1 Jan 14 and 1 Feb 14, 1 Feb 14 and 1 Mar 14, etc,...?

itemnumbalancemonth
231 Jan-14
2101 Feb-14
261 Mar-14
241 Apr-14
281 May-14

Thanks.

11 Replies
MVP
MVP

Re: add values of a column for the start of each month

It should be something like:

Load

          Sum(If (....

From <> Group By ....;

MVP
MVP

Re: add values of a column for the start of each month

Hi

Something like

T_Data:

LOAD intemnum,

  balance,

  Date(MonthStart(date#(month, 'd mmm-yyyy'))) As month

Inline

[

  intemnum, balance, month

  2, 3, 1 Jan-14

  2, 10, 1 Feb-14

  2, 6, 1 Mar-14

  2, 4, 1 Apr-14

  2, 8, 1 May-14

];

Data:

LOAD *,

  If(itemnum = Previous(itemnum), Alt(Previous(balance), 0), 0) + balance As cum2months

Resident T_Data;

DROP Table T_Data;

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ravic906
Contributor III

Re: add values of a column for the start of each month

Hi,

 


1:

load * inline
[
itemnum,balance,month
2,3,1-Jan-14
2,10,1-Feb-14
2,6,1-Mar-14
2,4,1-Apr-14
2,8,1-May-14
]
;
2:
load *,date(Date#(month,'DD-MMM-YY'),'M/D/YY') AS DATE
Resident 1;
DROP Table 1;

DROP Field month;

LOAD if(Previous(DATE)=AddMonths(DATE,-1),balance+Previous(balance),0) as new
Resident 2;

HTH.

Ravi N.

Not applicable

Re: add values of a column for the start of each month

if I were to use aggr function, how do I go about it?

Not applicable

Re: add values of a column for the start of each month

hey thanks! but if I were to use aggr function, how do I go about it?

Not applicable

Re: add values of a column for the start of each month

hey thanks! but if I were to use aggr function, how do I go about it?

MVP
MVP

Re: add values of a column for the start of each month

You mean, want this in the front -end. Aggr() might not be required. Create a sample qvw and share here explaining the expected output.

Not applicable

Re: Re: add values of a column for the start of each month

Hi, this is the expected output:

itemnum

balancemonthavg balance
231 Jan-146.5
2101 Feb-148
261 Mar-145
241 Apr-146
281 May-14-

where 'avg balance'=sum(balance)/2 for 1 Jan-14 and 1 Feb-14, 1 Feb-14 and 1 Mar-14, etc,...

MVP
MVP

Re: Re: Re: add values of a column for the start of each month

If(rowno()<>NoOfRows(), RangeAvg(Sum(balance), Above(Sum(balance),-1)))

PFA

Community Browser