Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - Do you have any example of how to loop through and get what I need. My requirement is:
I have a table already loaded with following fields
Date Value
16/01/2012 10
17/01/2012 203
18/01/2012 495
19/01/2012 494
20/01/2012 493
23/01/2012 494
and so on (note that this table only has working days, not the holidays or weekends)
I need to build a table so that it gets all these with current option and then the last 15 days (ie., records) with rolling period. For ex
Date AsOfDate AsOfType
16/01 16/01 Current
16/01 16/01 Rolling
17/01 17/01 Current
17/01 16/01 Rolling
17/01 17/01 Rolling
18/01 18/01 Current
so on , The Rolling should only go back to last 15 days, all should exist in the resident table (one above)
I tried to build this using IterNo() and peek() does not seem to get what I need...
AsOf:
Load
TransactionDate as AsOfDate,
'Current' as AsOfType,
TransactionDate
Resident DailyRevenue;
Concatenate (AsOf)
load ....
here I used IterNo() with while condition and peek() in the load statement but not really got what I need.
Any help please.
Hi,
Try this script
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Let vMin = num(makedate(2011,01,01));
Let vMax = num(MakeDate(2011,03,31));
Data:
Load date($(vMin) +RowNo() -1) as Date,
ceil(Rand() * 1000 + 450) as Value,
'Current' as AsOfDay
AutoGenerate 1
While date($(vMin) +RowNo() -1) < date($(vMax));
Temp:
Load max(Date) as MAX Resident Data;
let vMaxDate = (peek('MAX',0,'Temp')-15);
let vCount = FieldValueCount('Date');
for i=0 to $(vCount) -1
if num(peek('Date',$(i),'Data')) >= $(vMaxDate) then
load peek('Date',$(i),'Data') as Date,peek('Value',$(i),'Data') as Value,'Rolling' as AsOfDay
AutoGenerate 1;
ENDIF
next
Have a look at the QVW too
Regards,
Kaushik Solanki
Thanks for your quick reply, but this is not exactly what I was looking for. I need Date, AsOfDate and AsOfType as shown above. But I will try to work out but in the meantime if you have any logics which does then pl let me know.
Hi - I am nearly there, if you look at this example, it takes always the last 15 for rolling instead of to work out from the TransactionDate value. I think as per this code, this is wrong. How do I get the last 15 from the TransactionDate instead of always the last 15 for Rolling.
Data:
Load TransactionDate,
TransactionDate as AsOfDate,
'Current' as AsOfType
Resident DailyRevenue;
Temp:
Load max(TransactionDate) as MAX Resident Data;
let vMaxDate = (peek('MAX',0,'Temp')-15);
let vCount = FieldValueCount('TransactionDate');
for i=0 to $(vCount) -1
if num(peek('TransactionDate',$(i),'Data')) >= $(vMaxDate) then
load TransactionDate,
peek('TransactionDate',$(i),'Data') as AsOfDate,
'Rolling' as AsOfType
Resident Data
order by TransactionDate desc;
ENDIF
next
For ex, the output has these records
12/04/2012 | 13/04/2012 | Rolling |
11/04/2012 | 12/04/2012 | Rolling |
11/04/2012 | 13/04/2012 | Rolling |
etc,
these should not have in the final table, instead the last 15 dates for Rolling from 12/04 or 11/04 accordingly.
Thanks kaushik. Managed to get it using the iterno and while loop only. Just in case if anyone has any issues in terms of how to do Rolling Average using load script instead of RangeAvg on the chart. As you know RangeAvg will only work with the data that you selected on the charts, not outside which might be a problem. Therefore it's better to do using the load script and then you can easily display the moving average on the chart even for the data that's not visible in the chart.
A couple of steps:
a) Just reload your original data (going back to 90 days or 30 days) based on the requirement using IterNo() and While loop, ex
Rev_90days:
load Value,
Team,
dayname(TransactionDate,IterNo()-1) as TransactionDate
Resident RevRawData
while dayname(dayname(TransactionDate,-90),IterNo()-1) <= TransactionDate;
b) Secondly, just agreegate on this new table to get the Moving Average.
Rev_90DaysAvg:
NoConcatenate
load TransactionDate,
Team,
sum(Value)/count(TransactionDate) as Rev_Avg
Resident Rev_90days
where TransactionDate <= $(vMaxDate)
group by TransactionDate,
Team;
Hope this helps if anyone needs to do similar thing in your project.