topic days in months in QlikView

days in months

Fri, 12 Nov 2010 15:20:39 GMT

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

SV:days in months

gandalfgray — Fri, 12 Nov 2010 15:48:21 GMT

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.

days in months

pover — Fri, 12 Nov 2010 16:00:11 GMT

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.

days in months

Mon, 15 Nov 2010 14:54:59 GMT

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

days in months

Miguel_Angel_Baeyens — Mon, 15 Nov 2010 18:08:13 GMT

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

days in months

pover — Mon, 15 Nov 2010 18:51:52 GMT

Christoph,

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

Regards.

days in months

Wed, 17 Nov 2010 10:05:36 GMT

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!

days in months

Miguel_Angel_Baeyens — Wed, 17 Nov 2010 10:14:43 GMT

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.

days in months

Wed, 17 Nov 2010 10:21:34 GMT

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 😞

days in months

Miguel_Angel_Baeyens — Wed, 17 Nov 2010 10:38:56 GMT

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

days in months

Wed, 17 Nov 2010 11:37:02 GMT

This doesnt work, it returns Zero

Then i had a look into my Table structure and there is NO connection between MasterCalendar and Fakturadat (my Invoice date field)

shouldnt there be one? 🙂

i thought it gets the connection via these lines:

LET

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

;

LET

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

;

LET

varToday = num(today())

;

days in months

Miguel_Angel_Baeyens — Wed, 17 Nov 2010 11:51:39 GMT

Hello Christoph,

Of course there must be. From the posts above, I'm figuring you are using this piece of code

MasterCalendar:LOAD TempDate AS OrderDate, // Change this OrderDate to Fackturadat

That should link them now, so

Sum({< Year = {2010} >} Amount)

(Or the field equivalent to Amount) should return the right figure.

Regards.

days in months

Wed, 17 Nov 2010 12:05:25 GMT

Okay, connection established! 🙂

The amount is right, its the total of January 2010 minus the total of Jan 2009,

but MaxOrderDate still is 29.1.2010 😞

days in months

Miguel_Angel_Baeyens — Wed, 17 Nov 2010 12:24:47 GMT

Hi,

MaxOrderDate is one complete date field (day, month, year), and for me, it seems all right, because that was the last actual day you had sales on january (which also makes sense, January 30 and 31 were saturday and sunday, and likely not working days). In itself it doesn't represent any problem, the problem may come when you want to compare full months to full months on a date.

Then I do not recommend you to use max(field) but to work with the proper dimension in each case. Now that you have a calendar, there's no need to use date-wise comparisons, since you have months, years, weeks... and the proper comparison would be month to month.

Say you want to compare one month to the previous: March has 31 days and February 28 or 29, so date by date you will get an error when March 30 compares to February 30. Month to month will allow you to compare all March to all February, regardless the number of days each month has.

In addition to this, using set analysis will make your expressions easier to understand and to trace, an considerably faster than using any conditional. If you have already used set analysis, you know what I mean.

Hope this makes sense to you.

Do you anyway need that MaxOrderDate for any analysis that cannot be done using set analysis or month to month comparisons?

days in months

Wed, 17 Nov 2010 13:09:27 GMT

My target was to give our executives the possibility to get more information of sales WITHIN a month

but as it seems so difficult to include this option I will do my analysis just on monthly base (like you explained in your post)

Maybe I will try this later on 🙂

I use MaxOrderDate in almost all diagrams and tables to calculate Actual Year, Previous Year and the appropiate budget year

Thank you very much for this detailed explanation, you brought some light into the darkness 😃

days in months

Miguel_Angel_Baeyens — Wed, 17 Nov 2010 14:00:54 GMT

Hello Christoph,

Find attached an example of what I meant in my previous post. I've lodaded the file with some dummy data, and only display one chart that shows, per month, current year and previous year, for each day of the month.

If one day of current year has hadn't any sales, you will see 0, and so with the last year.

Hope it makes easier to understand.

days in months

Wed, 17 Nov 2010 14:35:04 GMT

Miguel, I understand what you want to tell me.

But I think I will stick to my MaxOrderDate logic as I always wanna show the actual + previous + budget year considering the users selections.

Furthermore I will concentrate on monthly comparison, in case of emergency (=D) i can show the daily sales (like in your example) and these also in cumulated version.

Thanks for the example, I think thats a general problem that there are such a low number of example-files. Almost all the demo files consist of just the simplest analysis 😞