14 Replies Latest reply on Apr 23, 2018 1:25 PM by Deepak Rai

# 12 months data from a given date

Attached is a sample workbook for a problem I am trying to solve.

I need to display the measures for only the last 12 months form the values entered in the parameter control called 'Metric Month'. ( Not from today() or current date)

The data consists of ticket details opened on a daily basis. My data source has a SNAP_Date to capture all tickets opened in that month.

But each month should be a calculated field which has data from last month 15 th to next month 14th only.

Any guidance is much appreciated!

Anusha H,

• ###### 1. Re: 12 months data from a given date

But each month should be a calculated field which has data from last month 15 th to next month 14th only.??? Is it different from what we did earlier?

• ###### 2. Re: 12 months data from a given date

Its the same Deepak Rai, I thought attaching a Workbook might explain things better.

I am not able to display the metric month and last 12 months simultaneously yet.

If you can help would be great !

Thanks,

Anusha

• ###### 3. Re: 12 months data from a given date

Hmmmmm...Check the last Solution we did...Is not you asking same thing? 15 to 14 any date

• ###### 4. Re: 12 months data from a given date

To add to my explanation, the display needs to look similar to attached.

Here a month-year combination is entered by the user and the 3 metrics ( in row) is displayed 12 months back from that month + the number in each month (cell) is tickets counted from 15th of its previous month to 14th of next month.

Thanks,

Anusha

• ###### 5. Re: 12 months data from a given date

Deepak Rai  Can you please make changes in the current workbook attached ? I need to see the solution work as a whole

• ###### 6. Re: 12 months data from a given date

So you mean to say that  for month selected in parameter it would display 15 previous  to 14 for selected month. That's what we did. I would do it this way then.

• ###### 7. Re: 12 months data from a given date

Yes, thats right I need each month defined as (15-14) and displayed for 12 previous months,  but when I applied that 12 month filter, correct values were not getting displayed.

• ###### 8. Re: 12 months data from a given date

ok will do wait and watch

• ###### 9. Re: 12 months data from a given date

Hi Anusha,

Here is the Solution. First you need to create 2 Calcs for last month's 15 and this Month's 14 To Set the Interval per Month when you select a Date from Parameter. So We Get this:

Also, I Set up a filter to go back 13 months from selected date and use those 13 months data to give you what you need.

So Last month's 15 Calc;

Above will fix your start per month date to the 15 of previous month

This month 14

Above will FIX your END point to 14th of any MONTH

So You Create This Calc To get what you need, you will get 13 Months in Display and you Can hide the First one to get only last 12 Month data for your Measures

So you can now get from above view the final view as:

Hide December, 15 in above so you need to hide first month, we need 13 Month's data as you are calculating from previous 15 to Current 14 in view.

Hope this Helps and you would Close Thread by marking it Helpful and CORRECT.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 10. Re: 12 months data from a given date

Hello Deepak Rai,

In the above Workbook if one of the Measures needs to be a running count , how do I get that ?

Thanks,

Anusha H.

• ###### 11. Re: 12 months data from a given date

RIght Click on measure in view>Quick Table Calc>Running Total

Deepak

• ###### 12. Re: 12 months data from a given date

Thanks Deepak Rai  that helped,

Trying to convert the table format into a line chart with one line for each measure.

Please see attached Line chart example which I am trying to replicate.

Thanks,

Anusha

• ###### 13. Re: 12 months data from a given date

Found it using this : Multiple Series On Line Graph

• ###### 14. Re: 12 months data from a given date

Thank God