topic Calculate Measure value for current week and prior week in Dashboards & Analytics Discussions

Calculate Measure value for current week and prior week

KHK — Wed, 28 Feb 2024 12:47:25 GMT

Hi ,

I have Period Name like Jan-24, using this column i want to calculate the measure value for current week and prior week.

Please let me know how to achieve?

Re: Calculate Measure value for current week and prior week

RADSr — Fri, 01 Mar 2024 13:35:16 GMT

To get a practical answer you'll need to provide some more information, but conceptually there are a few ways to get relative time measures.

One I've found effective is using an input date ( could be "today," or a prompted session variable, or another calculation ) to look up the period in the calendar and use that in a CASE statement ( more accurately w/in the case function in Incorta )

case(lookup(<period>, $current_date ) = 'Jan-24', <measure>, 0 )

You'd need to confirm business rules e.g. fiscal v Gregorian calendar, does current date mean *today* today, or yesterday because we loaded data overnight and yesterday is the most current, etc.

HTH

Re: Calculate Measure value for current week and prior week

RADSr — Fri, 01 Mar 2024 15:05:41 GMT

Here's a formula I did w/ the online store data - it's business logic is incomplete as it doesn't account for the year, but it'll give you a starting point.

The insight shows line total dollar amounts for January and zeros for everything else.

BIG NOTE: The time_dimension in the onlinestore schema defines the "id" field as its key. In order for this formula to work you need to set "id" to dimension and "db_date" as the key. The formula **will not error** if you don't, but will produce either nulls or the word #ERROR in your insight.

====================================

case(

lookup(

OnlineStore.time_dimension.month_name,

OnlineStore.time_dimension.db_date,

$SV_OrderDate,

'blah'

) = OnlineStore.time_dimension.month_name,

OnlineStore.salesorderdetail.LineTotal,

)