Calculating Data on Date time for future months.

[Anonymous]

Dear All ,

I need to calculate sum of data against date and Acounts such that for a particular month my output should be sum of the data residing in the months greater than selected months. (Needed In Scrip )

Data:

Month Acnt Data

Jan a1 100

Feb a1 101

Mar a1 102

Apr a1 103

Output :

Month Acnt Output

Jan a1 306

Feb a1 205

Mar a1 103

Apr a1 0

NOTE: I am able to achieve the same in front end using SET but i need the same in script level.

Kindly help

Regards

Ankit Tiwari

[tresesco]

hi,

you can follow the following method:

sort the data on MONTHNAME (format "Year Month"), then use the peek function recursively (refer the peek to itself) in the sorted resident data.

Regards, tresesco

[Anonymous]

Hi Tresesco,

Thanks for the reply .. can u explain in little detail how to achieve the same

[tresesco]

if the data is for several years, create a new field "Year Month" using MONTHNAME function with your date field, then sort(assuming the data is grouped month wise) in ascending order. when you finally load it you have to right the script something like :

Load Output as OrginalOutput,
 Previous(Output) + Peek('AggregatedOutput') as AggregatedOutput
Resident ......

.

I am not sure about the syntax of peek(quoates and second argument), please check it.

Regards, tresesco

[#5]

Hi Ankit,

I am having the same problem. Could you kindly tell how did you achieve this in the fron tend using SET ANALYSIS.

Thanks in advance.

Gaurav

[Anonymous]

Hi Gaurav,,

u can solve the same using set analysis in front end by storing the date in variable and picking data like this

Sum({<Month=,Year=,YearMonth=,Date1={">$(#vDate)"}>}Data)

where vDate is a variable that stores the date u need..

This will solve ur Problem.

Regards

Ankit Tiwari

[#7]

THanks Ankit.....

Regards,

Gaurav

[tresesco]

i have worked out the right expression(i hope so):

NumSum(Previous(Amount),Peek('FuturisticAmount')) as FuturisticAmount

Find the attached application. Hope this helps you.

Actually, now a days i am working in cognos, so don't have qlikview installed in the office system, so i would have to work in practical at home only, and then only i can come up with verified code.

Regards, tresesco

[Anonymous]

Tresesco,

Thanks a lot for your help ..

But i am not able solve the problem using the same . i am attaching part of the data . if u can try on the same.

the (amount is in Data field) and (date as Date1)

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8883.TAB1.rar:550:0]

Thanks a lot

[tresesco]

the script :

 Normal 0 false false false EN-IN X-NONE X-NONE MicrosoftInternetExplorer4
Table1:
LOAD //Amounts,
 Data as Amount1,
// BATCBRN,
// TAMT,
// COUNTER,
// BATCACTYR,
// BATCACTMN,
// POLNUM,
// RECNUM,
// SUBCLASSCODE,
// SACSCODE01,
 Year,
// Month,
 YearMonth
// Date1 as Date
FROM
C:\Users\Nivera\Downloads\8883.TAB1\TAB1.qvd // change to your your qvd path
(qvd);
Table2:
Load YearMonth as NameOfMonth,
Sum(Amount1) as Amount
resident Table1 Group By YearMonth Order By YearMonth desc;
Table3:
Load NameOfMonth as NewNameOfMonth,
Previous(Amount) as PreviousAmount,
NumSum(Previous(Amount),Peek('FuturisticAmount')) as FuturisticAmount
Resident Table2;
Drop Table Table1,Table2;

Hope this helps.

Regards, tresesco