SET ANALYSIS HELP

[marikabi]

Hello,

I'm trying to add a condition using set analysis to take into account only last 7 days.

This is the formula I'm using to calculate the "utilization" of our shops (and it's working fine):

MAX(AGGR(SUM(CUSTOMERS),PERIOD, SHOP))
/
FirstSortedValue(EMPLOYEES, -Aggr(SUM(CUSTOMERS),PERIOD, SHOP))
/
15

Period is a time dimension in the format: 'DD/MM/YYYY hh:mm:ss' (data are refreshed every 15 minutes)

I already defined a DATE dimension (DD/MM/YYYY) in the loading script.

Please can you guide me how to use set analysis to consider only last 7 days?

Thank you

[sunny_talwar]

May be this

Max({<DATE = {">=$(=Date(Today(1)-7, 'DD/MM/YYYY'))"}>} Aggr(Sum({<DATE = {">=$(=Date(Today(1)-7, 'DD/MM/YYYY'))"}>} CUSTOMERS), PERIOD, SHOP))
/
FirstSortedValue({<DATE = {">=$(=Date(Today(1)-7, 'DD/MM/YYYY'))"}>} EMPLOYEES, -Aggr(Sum({<DATE = {">=$(=Date(Today(1)-7, 'DD/MM/YYYY'))"}>}CUSTOMERS), PERIOD, SHOP))
/
15

[marikabi]

Hi Sunny, that's unfortunately not working.

I tried with a similar structure (but using max(DATE) as function to define the date), and doesn't work either.

Probably I shouldn't put the sets both in inner and outer aggregation, but can't find a combination that's working 😞

[marikabi · Accepted solution]

Hi Sunny,

I solved using

{<DATE={">=$(=Date(max(DATE)-7,'DD/MM/YYYY'))"}>}

probably just an issue with the spacing 🙂

The issue I have now is regarding the shops which closed -> eg in the last week I don't have any data for a specific shop, so I would expect to have 0% utilization, while I get the utilization for the last 7 days available (meaning 2 weeks ago).

How could I fix this?

Thank you again 

[sunny_talwar]

Not able to completely understand the problem without seeing it. Are you able to share an example where we can see the issue?

[marikabi]

okay, let's assume the last data load happened on June 16th.

the set we put in the function, limits the data to the last 7 days available --> june 10th till june 16th.

Shops A, B and C were regularly open in this period --> the formula works fine, taking into account the period june 10-16.

Shop C was closed in these dates, meaning that I didn't get any record from our database --> formula takes into account the period june 4th till june 10th, showing me the result for this period instead of 0 as I would expect.

Hope is clear now, otherwise will try to fake the data 🙂