topic Calculating Data on Date time for future months. in QlikView

Calculating Data on Date time for future months.

Anonymous — Thu, 24 Mar 2011 06:46:04 GMT

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

Calculating Data on Date time for future months.

tresB — Thu, 24 Mar 2011 07:12:41 GMT

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

Calculating Data on Date time for future months.

Anonymous — Thu, 24 Mar 2011 07:23:31 GMT

Hi Tresesco,

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

Calculating Data on Date time for future months.

tresB — Thu, 24 Mar 2011 07:50:37 GMT

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

Calculating Data on Date time for future months.

Thu, 24 Mar 2011 08:02:04 GMT

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

Calculating Data on Date time for future months.

Anonymous — Thu, 24 Mar 2011 08:49:53 GMT

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

Calculating Data on Date time for future months.

Thu, 24 Mar 2011 09:35:40 GMT

THanks Ankit.....

Regards,

Gaurav

Calculating Data on Date time for future months.

tresB — Thu, 24 Mar 2011 18:31:34 GMT

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

Calculating Data on Date time for future months.

Anonymous — Mon, 28 Mar 2011 08:38:49 GMT

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

Calculating Data on Date time for future months.

tresB — Mon, 28 Mar 2011 17:09:23 GMT

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

Calculating Data on Date time for future months.

Anonymous — Sat, 02 Apr 2011 05:08:04 GMT

Hi,,

Sor[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4774.Query.zip:550:0]ry for late reply,

This is working alright for only year months. I need futuristic amount for every Document (polnum) and other dimentions.

But as soon as i include polnum and other dimention data changes.

Kindly see if u can do anything on this.

Attached the application, how the data is needed .

Regards

Calculating Data on Date time for future months.

tresB — Sat, 02 Apr 2011 05:14:35 GMT

i can't go with application right now. let me try otherwise. What is your key in this table ?

Regards, tresesco

Calculating Data on Date time for future months.

Anonymous — Sat, 02 Apr 2011 05:30:40 GMT

Hi,

The data is needed against these dimentions.

where POLNUM is the document number.

the data against every POLNUM has to be calculated such that under one month say( July 2010) the sum of future months of POLNUM data is to be calculated and then displayed against date.

LOAD
Data,
BATCBRN,
POLNUM,
RECNUM,
SUBCLASSCODE,
SACSCODE01,
YearMonth,
Date1
FROM TAB1.qvd(qvd);

Regards

Ankit Tiwari

Calculating Data on Date time for future months.

tresB — Sat, 02 Apr 2011 05:54:08 GMT

let me know what output you expect :

POLNUM	Data	Dim1	Dim2	Date	YearMonth	OutPut
1	10			1/1/11	Jan 2011	?
2	10			2/1/11	Jan 2011	?
3	10			3/3/11	Mar 2011	?
4	10			4/3/11	Mar 2011	?
5	10			10/10/11	Oct 2011	?
6	50			11/10/11	Oct 2011	?

Calculating Data on Date time for future months.

Anonymous — Mon, 04 Apr 2011 07:37:26 GMT

Kindly find the data in two tabs inside excel sheet.

The first sheet has the data for all years while the second has only bifercation data for july 2010.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/6332.Needed-data.zip:550:0]

Warm Regards

Ankit Tiwari

Calculating Data on Date time for future months.

tresB — Wed, 06 Apr 2011 14:45:02 GMT

Hi, Still not clear to me(pardon my bad apprehension skill !). can you please give me a case, as i structured in my earlier post? it could have been easier if you would fill the "?????" in the above chart. it would help me understand the logic behind your aggregation.