days in months

[#1]

I have already posted this question into another thread, but it does not exactly belong to that topic.

Honestly, I do not know why i cant find a solution therefore in this forum, because I think its a major problem (or im just too stupid to understand that).

When I load my Sales into QV, the invoicedate is the date i am calculating with.

I do have the problem that there are no sales on the last two days of January 2010, so when i select just january, then my MaxOrderDate (=max(Invoicedate)) becomes 29th January.

Within my analysis QV compares January 2010 with January 2009 but only 29 days of January, as a result the sales from 30st and 31st January 2009 are missing.

So i do have to tell QV that January ALWAYS HAS 31 DAYS, February 29 days, march 31 days and so on (always the maximum possible days)

how can i handle this?

Thank you a lot in advance, hope you can give desireable input this weekend so i can finalize my sales analysis next week 🙂

Regards, Christoph

the thread I have already posted is http://community.qlik.com/forums/p/26358/144044.aspx#144044

[gandalfgray]

Hi

If you do not already have a "Calendar" table in your app, create one, for example like this:

Calendar:
LOAD DISTINCT
 OrderDate,
 Year(OrderDate) as Year,
 Month(OrderDate) as Month,
 Date(Monthstart(OrderDate), 'MMM-YYYY') as YearMonth,
 'Q' & Ceil(Month(OrderDate)/3) as Quarter,
 Dual(Year(OrderDate) & '-Q' & Ceil(Month(OrderDate)/3), Year(OrderDate) & Ceil(Month(OrderDate)/3)) as YearQtr,
 Week(OrderDate) as Week
RESIDENT OrderHeader;

Replace OrderHeader and OrderDate with your sales table name resp. your sales date field name.

If you then select Month = January you should get all January-sales regardless of year and day.

[pover]

Christoph,

If you're comparing a month of the current year with the month of the past year, there is really no need to use the exact date. You should compare them using the January that you exact using the month() function.

Independently, you have to use the function monthend() to make sure QV always considers all the days of the month. Alternatively you can create a master calendar that uses the min and max date of your data and creates creates a table with all dates like the example below.

LET varMinDate = Num(Peek('OrderDate', 0,
'Orders'));

LET varMaxDate = Num(Peek('OrderDate', -1,
'Orders'));

LET varToday = num(today());

TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS
TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS OrderDate,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS
MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(varToday), 0) * -1 AS
CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS
LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;

Regards.

[#4]

Göran, with your solution there is also no Creation (of 31st January for example) of dates included

Karl, I do have to include a calender-solution (which creates every possible day). I am not able to just switch the formula as I also want to compare YTD 24.1.2010 with YTD 24.1.2009 for example.

I just need to teach QV that every Jannuary has 31 days, every February 28 days and so on ..

I would guess your calender-suggestion would do that, but unfortuantely I am not able to include your calender into my Files (maybe i'm still too much novice in QV or too stupid 🙂 )

I have adapted your first three lines and then tried to get it to work but either the script has an error or the calender table is not linked to my sales table

Does the calender tab in the script need to be the first tab loaded or the last one?

I have adapted your calender this way:

LET

varMinDate = Num(Peek('Fakturadat', 0,Sales))

;

LET

varMaxDate = Num(Peek('Fakturadat', -1,Sales))

;

LET

varToday = num(today())

;

Thanks a lot for your effort, Karl!

Regards,

Christoph

[Miguel_Angel_Baeyens]

Hello Christoph,

You may use MonthEnd() in conjunction with Day function to get dynamically the number of total days per month

=Day(MonthEnd(YourDateField))

With this, you also save the leap years. But it depends on how you are working with your data, and I'd definitely bet for the master calendar approach.

Hope it helps

[pover]

Christoph,

Post the exact errors you are seeing and we will help you make the master calendar.

Regards.

[#7]

Thanks for the suggestion Baeyens, I will keep that in mind but primary go for the calender.

Thanks for your offer Karl, I have included your calender in my script (as the last tab), I adapted the first three "LET ...." and the rest should work automatically, but it doesnt 😞

So when i load the script the first error I get is:

Fehler in Skriptzeile: (-> Error in script-row)
TempCalendar:
LOAD
+ rowno() - 1 AS Num,
date( + rowno() - 1) AS
TempDate
AUTOGENERATE
- + 1

then i say okay, then the next error message is:

Tabelle nicht gefunden (-> table not found)
MasterCalendar:
LOAD TempDate AS OrderDate,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS
MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, 40499, 0) * -1 AS
CurYTDFlag,
inyeartodate(TempDate, 40499, -1) * -1 AS
LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC

Thanks a lot!

[Miguel_Angel_Baeyens]

Hello Christoph,

It seems that you need to quote the name of the table in the LET statement:

LET varMinDate = Num(Peek('Fakturadat', 0, 'Sales'));

Hope this allows you to run the script properly.

[#9]

that was a fast reply! 🙂

Hm i thought i had tried all different variations (the QV help says that just the fieldname has an ' at start and end), but yes that was the mistake.

Nevertheless my problem is still there, when I choose January, MaxOrderDate becomes 29.1.2010 instead of 31.1.2010

I need anything additionally like makedate or an applymap (map which shows the maximum days of a month), am I right?

Hmmmm 😞

[Miguel_Angel_Baeyens]

Hello Christoph,

Let's try a different approach, but based on the master calendar table. Just for testing to see whether this returns the right values, create a new text object and use the following

=(Sum({< Year = {2009}, Month = {'jan'} >} Amount) - Sum({< Year = {2010}, Month = {'jan'} >} Amount))

This will take the whole Amount recorded for the month of january 2009 (from the first day of Amount to the last day of Amount) and will substract the corresponding amount for 2010, regardless of the number of days of sales.

If you want to get an average sale amount per day per month, you may use something like

Sum(Amount) / Day(MonthEnd(MakeDate(2010, 1, 1)))

Instead of the bold "1" you can set a field with the numeric value of the month or a variable with it.

Hope this helps