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

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

brunopaulo — Wed, 25 Nov 2020 16:16:04 GMT

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

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

sunny_talwar — Wed, 27 Sep 2017 11:55:10 GMT

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

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

brunopaulo — Wed, 27 Sep 2017 11:57:07 GMT

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

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

sunny_talwar — Wed, 27 Sep 2017 12:03:08 GMT

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

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

brunopaulo — Wed, 27 Sep 2017 23:37:09 GMT

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