topic last 12 months data based on Period selection using prompt value in Dashboards & Analytics Discussions

last 12 months data based on Period selection using prompt value

KKR — Thu, 07 Mar 2024 11:52:22 GMT

Hi,

I have requirement like if i select Period Name as Mar-2024 in prompt , dashboard should return data from Feb -2023 to Mar-2024.

if i select Period Name as Feb-2024 in prompt , dashboard should return data from Jan -2023 to Feb-2024,

Please let me know how to achieve this in graph?

Re: last 12 months data based on Period selection using prompt value

RADSr — Thu, 07 Mar 2024 15:13:17 GMT

You need the period name text for which you want to prompt to be associated with a date ( or something calculable ) so you can create filter logic something like

AND ( <transactiondate> <= <datefromprompt> , <transactiondate> >= add_months(<datefromprompt>, -12 )

*** the above is using "date" but you can use month begin date, month end date, yearmonth, etc.

If you don't yet have a calendar dimension in your model you will want one. It makes relative time stuff approximately a million times easier than not having one 😉

Re: last 12 months data based on Period selection using prompt value

RADSr — Fri, 08 Mar 2024 00:10:28 GMT

Here's a quick example using Online Store which isn't universal ( I created the date via formula ) but will give an idea of how it can be accomplished.

I'll add that if this is for a customer and not just an authoring exercise it is very important to get the requirements correct - and that starts with the business. Imagine in the screen cap below the consumer had chosen 2024-03 for the reporting period -- the insight would then be comparing 11 *full* months, to 1 month only 7 days old. It may be that the actual requirement is for a last 12 full month comparison.

I'll also emphasize that if I were designing something like this for a customer I would start at the schema and import or create a date dimension. Relative time and other date manipulations are much easier, you can incorporate fiscal calendars, company holidays, etc. Not to mention acting as a conformed dimension for stitch queries.

HTH,

Re: last 12 months data based on Period selection using prompt value

KKR — Fri, 08 Mar 2024 14:53:55 GMT

@RADSr Thank you for your information.