Point in Time Reporting (Allocated memory exceeded or high values)

[brunopaulo]

Hello Community,

Im trying do a comparison from the values of sales from the select period (can be year and month) vs previous month or year. When nothing selected should calculate the max year and max month.

I've created this master calendar:

minmaxdates:

LOAD

min([Data]) as minDate,

max([Data]) as maxDate

Resident [Main Data];

LET vMinDate = Num(Peek('minDate',0,'minmaxdates'));

LET vMaxDate = Num(Peek('maxDate',0,'minmaxdates'));

DROP TABLE minmaxdates;

DateRanges:

LOAD

$(vMinDate) + IterNo()-1 as dateNum,

Date($(vMinDate) + IterNo()-1) as TempDate

AutoGenerate 1 While $(vMinDate) + IterNo()-1<=$(vMaxDate);

[Master Calendar]:

Load *,

AutoNumber(Period, 'PeriodID') as [PeriodID],

AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID];

LOAD Distinct

TempDate   as [Data],

Day(TempDate)   as Day,

Week(TempDate)   as Week,

Month(TempDate)   as Month,

'Q' & Ceil(Month(TempDate)/3) as Quarter,

Year(TempDate)   as Year,

Year (TempDate)*100 + Month(TempDate) as Period

Resident DateRanges Order by TempDate asc;

DROP TABLE DateRanges;

Then i create a set of variables. In this particular example i have:

vSetMTD is defined by

PeriodID = {"<=$(=Max(PeriodID))"}, Year = , Quarter = , Period = , Month =

vSetPreviousMonth is defined by

PeriodID={$(=Max(PeriodID)-1)}, Year= ,  Quarter = ,  Period = ,  Month =

Then to display my KPI i do (gauge chart)

(Sum({$<$(vSetMTD)> } [Valor]) / Sum({$<$(vSetPreviousMonth)> } [Valor)) - 1

And this give me the memory problem.

Thank's in advance

Best Regards

Bruno Paulo

[sunny_talwar]

Would you be able to share a sample to look at this?

[brunopaulo]

Sorry for the trouble, meanwhile i solved it. Some variables have syntax problems

[sunny_talwar]

That is great can you share what you had to fix in order to get it working?

[brunopaulo]

Hello, sorry for delay, the script is the same

vSetMTD

PeriodID = {$(=Max(PeriodID))},

Year = ,

Quarter = ,

Period = ,

Month =

vSetPreviousMonth

PeriodID = {$(=Max(PeriodID) - 1)},

Year = ,

Quarter = ,

Period = ,

Month =

I'm not sure but i think the problem was here.

If anyone try this and give u problem please contact me.

Best Regards

Bruno Paulo