Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Selecting previous month's purchases

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!

8 Replies
sunny_talwar

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.

Not applicable
Author

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)

tamilarasu

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)

stabben23
Master
Master

Hi, Try this if you want to use Date:

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

Not applicable
Author

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

Not applicable
Author

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

tamilarasu

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.

sunny_talwar

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