cancel
Showing results for
Did you mean:

## Calculate Measure value for current week and prior week

Partner

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?

2 REPLIES 2
Captain

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

-- IncortaOne@PMsquare.com --
Captain

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,
0
)
-- IncortaOne@PMsquare.com --