Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
brunopaulo
Partner - Creator II
Partner - Creator II

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

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

4 Replies
sunny_talwar

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

brunopaulo
Partner - Creator II
Partner - Creator II
Author

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
Partner - Creator II
Partner - Creator II
Author

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