topic Last 12 months in QlikView

Last 12 months

Thu, 22 Sep 2011 03:00:14 GMT

Hi,

I would like to show last 12 months in my chart dimension. Presently my data is covering dates from 2008 until now and chart is congested. I just learned about flags but unable to locate the correct formula. Is there anybody who could help me? How can I show it at month level instead of day?

(I am not familiar with set analysis yet, therefore it could be too difficult for me to understand andexecute it at this moment).

Best regards,

piroglu1907

Last 12 months

Anonymous — Thu, 22 Sep 2011 06:56:19 GMT

Depending on how your date-field looks like you can use something like this in your loading script:

date(monthstart(YourDateField), 'MMM-YYYY') AS MonthYear,

or:

date(monthstart(YourDateField), 'YYYY-MM') AS YearMonth,

This will create a new field of YearMonth (or MonthYear whatever you prefer) with whiche you can count.

While you are at it, it might be even bete to create a complete calender
here is an example of some options:

YourDateField,

YourDateField AS datum,

week(YourDateField) AS Week,

year(YourDateField) AS Year,

month(YourDateField) AS Month,

day(YourDateField) AS Day,

weekday(YourDateField) AS WeekDay,

'Q' & ceil(month(YourDateField) / 3) AS Quarter,

date(monthstart(YourDateField), 'MMM-YYYY') AS MonthYear,

date(monthstart(YourDateField), 'YYYY-MM') AS YearMonth,

week(YourDateField) & '-' & year(YourDateField) AS WeekYear,

Hope this is what you are looking for.

Dennis

Last 12 months

Fri, 23 Sep 2011 01:02:23 GMT

Dear Dennis,

Thanks for your helpful answer. I think I need to build a habit to have and maintain a proper calendar in all projects. I have applied your suggestion and now able to focus on data related fields from different perspectives. (best performing day of week or month etc.)

For my last 12 months' display query, I thought of the following statement which seems to be working fine.

Where (created_date)>= AddMonths(Today(),-11);

However, it applies to all the date fields in my records - which is fine for now but we may not want for other projects. Would you have any better suggestions for it?

Best,

piroglu1907

Last 12 months

Anonymous — Mon, 26 Sep 2011 08:52:16 GMT

I am not sure if I understand your question corretly.

With the "Where" statement in your loading script it only loads data from the last year.

If you want more data just adjust or delete it and use these kind of statement in your charts (edit dimensions or expressions).

Re: Last 12 months

SunilChauhan — Mon, 26 Sep 2011 09:33:45 GMT

hope this will give you correct answer.

please see it once

Re: Last 12 months

Tue, 27 Sep 2011 01:01:40 GMT

Dear Sunil,

Thank you very much. I am not familiar with set analysis yet so it is hard for me to understand the logic at least for the moment. However, your expression is delivering what I was looking for.

Thank again!

Best regards,

piroglu1907

Re: Last 12 months

Tue, 27 Sep 2011 01:03:46 GMT

Hello Dennis,

You are right. I guess it was not the best solution to filter everything at the very beginning. I saw an example from Sunil at this thread which came handy. As you mentioned, he used the statement inside the expressions to view required time frame.

I appreciated your help and replies; thank you very much!

Best regards,

piroglu1907