Last 12 months

[#1]

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

[Anonymous]

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

[#3]

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

[Anonymous]

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

[SunilChauhan · Accepted solution]

hope this will give you correct answer.

please see it once

[#6]

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

[#7]

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