topic Re: Chart Showing Last 12 Months based on Max Date filter in App Development

Chart Showing Last 12 Months based on Max Date filter

millsaz69 — Thu, 26 Dec 2019 17:39:32 GMT

I am trying to build a line chart showing each month for the last 12 months count of the "LeadType" value. When the user selects the dates from the filter pane, the Max(Date) would be used to determine the last 12 months. I've found so many different ways people have done similar calculations, but none seem to work.

Here is my last attempt before posting the question:

Dimension:

=[Date.autoCalendar.MonthYear]

Measure:

=Count({$<MonthsAgo={">=0<12"}>} LeadType)

Re: Chart Showing Last 12 Months based on Max Date filter

Taoufiq_Zarra — Thu, 26 Dec 2019 19:10:54 GMT

can you share a sample data ?

Re: Chart Showing Last 12 Months based on Max Date filter

millsaz69 — Thu, 26 Dec 2019 19:26:30 GMT

Unfortunately, I am not able to post the data to the internet. I just need the total count of the "LeadTypes" for each month for 12 months based on the max date selected by the user in the filter pane.

Here is the format of the data:

LeadDate, LeadType

'MM/DD/YYYY', NEWSLETTER

Lead Types

NEWSLETTER

CATALOG

CONTACT

SAMPLE

DOWNLOAD

Re: Chart Showing Last 12 Months based on Max Date filter

millsaz69 — Thu, 26 Dec 2019 19:37:53 GMT

This is the current bar chart showing the filtered months. I just need it to show prior 12 months from max date (Dec 2019).

Re: Chart Showing Last 12 Months based on Max Date filter

millsaz69 — Thu, 26 Dec 2019 20:34:27 GMT

I have tried this expression and when I select a date in the filter pane it filters the data.

count({<vMaxDate = {"$(='>=' & Date(AddYears(vMaxDate, -12), 'MM/DD/YYYY') & '<=' & Date(vMaxDate, 'MM/DD/YYYY'))"}>}LeadType)

Re: Chart Showing Last 12 Months based on Max Date filter

Taoufiq_Zarra — Thu, 26 Dec 2019 21:39:59 GMT

sample data is necessary to understand. But that's okay.

Are you only looking for the last 12 months counts, I suggest you to compare the dates and not the months.

Is this formula okay ?

=Count({$<DateAgo= {">=$(=AddMonths(Date(Today(),'MM/DD/YYYY'),-12))"}>} LeadType)