Require advise regards to last day of Week, Month, Quarter and Year (Period).
Some context, this is to calculate the Outstanding vol. The daily OS vol has not issue.
however when the pivot chart cyclic dimension change to Week or Month. it sum up all the OS vol.
the expected value, it should show the last day OS vol.
Weekly Desire output
Ideally the set analysis should work for Month, quater and year in the Cyclic Dimension.
MTK_DATE is the Day/Date. Week, Month, Quarter and Year are derived from MTK_DATE.
It is possible to use the below and resolve the issue?
WORK_IN - WORK_OUT
, -Aggr(MTK_DATE, MTK_DATE)
The last solution would be create a Flag for each period, and the set anaylsis would use a couple of IF to check cyclic options and perform the right set analysis. I find that is the raw way and feel there should have a better way