topic Re: Problem with a Date related Sum in QlikView

Problem with a Date related Sum

Tue, 01 Dec 2015 11:29:59 GMT

I have a report which gives Daily and Month to Date sales figures. As we've just gone into December it's thrown up a problem with the a few of the formulas which are based on the number of days in the month. For example:

//Month To Date

count({$<Orders.CalendarMthNo={$(vDateMaxMonth)},Orders.CalendarYr={$(vDateMaxYear)}, Orders.DayOfYear = {'<$(vDateMaxDayOfYear)'}>}distinct order_id)

//Budget MTD

sum({1<Orders.CalendarYr={$(vDateMaxYear)},Orders.CalendarMthNo={$(vDateMaxMonth)}>} ((Budget_Distinct_Order_id/vDateTotalDaysInMonth)*vDateMaxDayOfMonthLessOne))

With the variables being:

vDateMaxYear: =max(Orders.CalendarYr)

vDateMaxDayOfYear : =DayNumberOfYear(max(order_date))

vDateTotalDaysInMonth: =Day(monthend(max(order_date)))

vDateMaxDayOfMonthLessOne: =max(order_date)-1

These worked fine all month, but this morning they didn't populate and I'm 99% certain it's because of the change in month.

I'm also pretty certain that if I could figure out how to create a variable that would give me the month for yesterday it should sort out most of the issues, but for some reason it just doesn't seem to like anything I try.

Any help would be very gratefully received!

Ian

Re: Problem with a Date related Sum

anlonghi2 — Tue, 01 Dec 2015 17:51:42 GMT

Hi Ian,

how do you calculate vDateMaxMonth ?

Regards

Andrea

Re: Problem with a Date related Sum

Wed, 02 Dec 2015 08:54:35 GMT

Sorry - thought I might miss one...

max({<Orders.CalendarYr={$(vDateMaxYear)}>}Orders.CalnerdarMth)

Re: Problem with a Date related Sum

anlonghi2 — Fri, 04 Dec 2015 17:33:02 GMT

The problem could be in the formula used to calculate vDateMaxDayOfMonthLessOne because at the beginning of the month max(order_date)-1 return 0 (if order_date is equal to the number of the month day) or the last day of the previous month (if order_date is a valid date) and so the result is a zero or null value.

Let me know

Andrea

Re: Problem with a Date related Sum

Mon, 14 Dec 2015 12:23:41 GMT

That was it - thanks!

Re: Problem with a Date related Sum

anlonghi2 — Mon, 14 Dec 2015 13:16:40 GMT

You are welcome.

Best regards

Andrea