topic Re: Selecting previous month's purchases in QlikView

Selecting previous month's purchases

Wed, 25 Nov 2020 16:16:04 GMT

Hi All!.

I'm creating a model on stock ageing, and I need to pull through the Purchases for the previous calendar month into this model. I'm trying with this expression but I am getting zeroes;

=Sum({<DteGRN={">=$(=AddMonths(MonthStart(Max(num(Date))),-1))<=$(=AddMonths(Max(num(Date)),-1))"},TheMonth=, TheYear=>} TotalCost)

DteGRN is the date field in my PurchaseFact SQL Table that I am using, and hooking in a TimeDimension table as well.

Can anybody by anychance please help?

Many thanks!

Re: Selecting previous month's purchases

sunny_talwar — Tue, 13 Dec 2016 16:33:18 GMT

What is the format of DteGRN field? Assuming it is MM/DD/YYYY, try this:

=Sum({<DteGRN={">=$(=Date(MonthStart(Max(Date), -1), 'MM/DD/YYYY'))<=$(=Date(AddMonths(Max(Date), -1), 'MM/DD/YYYY'))"}, TheMonth=, TheYear=>} TotalCost)

Change to your date format accordingly.

Re: Selecting previous month's purchases

Wed, 14 Dec 2016 06:36:49 GMT

Thanks for your mail Sunny T - unfortunately did not work. This is what I used, changing the format to YYYY/MM/DD;

=Sum({<DteGRN={">=$(=Date(MonthStart(Max(Date), -1), 'YYYY/MM/DD'))<=$(=Date(AddMonths(Max(Date), -1), 'YYYY/MM/DD'))"}, TheMonth=, TheYear=>} TotalCost)

Re: Selecting previous month's purchases

tamilarasu — Wed, 14 Dec 2016 06:51:21 GMT

Hi Christopher,

Try this,

=Sum({<DteGRN={">=$(=Date(MonthStart(Max(Date), -1), 'YYYY/MM/DD'))<=$(=Date(MonthEnd(AddMonths(Max(Date), -1)), 'YYYY/MM/DD'))"}, TheMonth=, TheYear=>} TotalCost)

Re: Selecting previous month's purchases

stabben23 — Wed, 14 Dec 2016 06:55:49 GMT

Hi, Try this if you want to use Date:

Sum({<DteGRN={">=$(=MonthStart(addmonths(Max(Date), -1))<=$(=MonthEnd(AddMonths(Max(Date), -1))"}, TheMonth=, TheYear=>} TotalCost)

Re: Selecting previous month's purchases

Wed, 14 Dec 2016 07:50:21 GMT

Thanks, but I'm afraid it didn't work

Re: Selecting previous month's purchases

Wed, 14 Dec 2016 07:51:08 GMT

Thanks a mil, I'm afraid it didn't work

Re: Selecting previous month's purchases

tamilarasu — Wed, 14 Dec 2016 07:57:38 GMT

I guess you need to format your date field DteGRN while loading the data. Try by changing your load script like below

Date(Date#(DteGRN,'Source file format'),'YYYY/MM/DD') as DteGRN

If that's not the problem, please post a sample.

Re: Selecting previous month's purchases

sunny_talwar — Wed, 14 Dec 2016 09:03:45 GMT

Is DteGRN a date or timestamp? Would you be able to share a sample to look at this?