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

[#2]

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())

;

[Miguel_Angel_Baeyens]

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.

[#4]

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 😞

[Miguel_Angel_Baeyens · Accepted solution]

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?

[#6]

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 😃

[Miguel_Angel_Baeyens]

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.

[#8]

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 😞