Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a financial data set that includes information that can be reported on a single day, a whole fiscal month, or a fiscal week (Sunday-Saturday). I am looking to visualize this data on a pivot table that is broken down by month and by week.
I created an expression that displays fine on a standard KPI, but shows as null on the pivot table. This happens when I have a single month selected or two consecutive months. I suspect this has something to do with some of the fiscal week data crossing two different months, e.g. October 29th 2023 - November 4th 2023.
I'm trying to write it so that if I only have a single month selected it takes into account only financial data that falls within the selected month. Any help would be appreciated, thanks!
sum({<[DataType] = {"Revenue"}, [Data.Date]={">=$(=monthstart(max({$} Today(1))))<=$(=monthend(addmonths(max({$} Today(1)),0)))"}
Aggr(max([Data.DailyRevenue]), [Data.ID], [Data.Date], Data.Customer))
It sounds like you need to implement a master calendar for your fiscal periods.
Then you can have a Fiscal Month & Fiscal Week dimensions in your pivot.
-hth
ws
We are implementing a master calendar in the pivot that has Fiscal Month and Fiscal Week included. The pivot itself works with all of the other measures that we are calculating on, but perhaps something needs to be changed in the load script for the data for this particular table?
MasterCalendar:
Load
*,
Date as PostedDate,
Date as ActualShipDate,
Dual(Quarter &'-'& Year, Year & QuarterNumber) as QuarterYear, //Dual used for sorting in charts.
Dual(Month &'-'& Year, Year & Num(Month, '00')) as MonthYear, //Dual used for sorting in charts.
Num(Month) as MonthNum;
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
ceil(month(TempDate) / 3) AS QuarterNumber,
//Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
weekend(TempDate) as WeekEnd; //Used for week ending calculations;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Add a year to maxdate so we have some future dates to select ===/
LOAD
mindate,
Date(AddYears(maxdate, 1)) as maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Date', recno()))-1 as mindate,
max(FieldValue('Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Date');
Drop table Link;
Adhoc_temp:
LOAD
ID,
// these fields probably aren't needed
"Start time",
"Completion time",
Email,
Name,
"Last modified time",
// these are
Account as ClientCommonName,
"Date of Adhoc Revenue or Expense" as Date,
"Adhoc Type" as AdhocType,
"Adhoc Expense Amount" as AdhocExpenseAmount,
"Adhoc Revenue Amount" as AdhocRevenueAmount,
Facility,
"Expense Detail" as ExpenseDetail,
"Revenue Detail" as RevenueDetail,
"Expense or Revenue Distribution" as Distribution
FROM [AdhocData.xlsx]
(ooxml, embedded labels, table is Form1);
Adhoc_monthly_temp:
IntervalMatch(Date)
Load Distinct
MonthStart(Date) as PeriodStart,
MonthEnd(Date) as PeriodEnd
Resident Adhoc_temp;
Join(Adhoc_monthly_temp)
LOAD
ID,
// these fields probably aren't needed
"Start time",
"Completion time",
Email,
Name,
"Last modified time",
// these are
ClientCommonName,
AdhocType,
AdhocExpenseAmount,
AdhocRevenueAmount,
Facility,
ExpenseDetail,
RevenueDetail,
Distribution,
MonthStart(Date) as PeriodStart,
MonthEnd(Date) as PeriodEnd
Resident Adhoc_temp
Where Distribution = 'Fiscal Period';
Adhoc_weekly_temp:
IntervalMatch(Date)
Load Distinct
WeekStart(Date) as PeriodStart,
WeekEnd(Date) as PeriodEnd
Resident Adhoc_temp;
Join(Adhoc_weekly_temp)
LOAD
ID,
// these fields probably aren't needed
"Start time",
"Completion time",
Email,
Name,
"Last modified time",
// these are
ClientCommonName,
AdhocType,
AdhocExpenseAmount,
AdhocRevenueAmount,
Facility,
ExpenseDetail,
RevenueDetail,
Distribution,
WeekStart(Date) as PeriodStart,
WeekEnd(Date) as PeriodEnd
Resident Adhoc_temp
Where Distribution = 'Fiscal Week'
And NOT(IsNull("Start time"));
Adhoc_temp2:
NoConcatenate Load
*,
MonthStart(Date) as PeriodStart,
MonthEnd(Date) as PeriodEnd
Resident Adhoc_temp
Where Distribution = 'Specific Date';
Concatenate
Load
*
Resident Adhoc_monthly_temp
Where not match(WeekDay(Date),'Sat','Sun');
Concatenate
Load
*
Resident Adhoc_weekly_temp
Where not match(WeekDay(Date),'Sat','Sun');
Drop table Adhoc_temp;
Drop table Adhoc_monthly_temp;
Drop table Adhoc_weekly_temp;
Qualify *;
Unqualify %ClientCommonName_Date_Key;
Adhoc:
Load
AutoNumber(Facility & '|' & ClientCommonName & '|' & Date) as %ClientCommonName_Date_Key,
*,
if(Distribution='Fiscal Week',AdhocExpenseAmount/5,if(Distribution = 'Fiscal Period', AdhocExpenseAmount/NetWorkDays(PeriodStart, PeriodEnd), AdhocExpenseAmount)) as DailyExpense,
if(Distribution='Fiscal Week',AdhocRevenueAmount/5,if(Distribution = 'Fiscal Period', AdhocRevenueAmount/NetWorkDays(PeriodStart, PeriodEnd), AdhocRevenueAmount)) as DailyRevenue
Resident Adhoc_temp2
//where ID > 7 // update to exclude test records
;
Unqualify *;
drop Table Adhoc_temp2;
That is a lot of code there and it would take some time to decipher.
I am not sure you are really leveraging your master calendar.
Your date of record should be the one linked to the calendar.
For any given date, it should have the calendar period &/or fiscal period it corresponds to. You can also have an attribute that flags where it is a workday or not.
So for "today", the link will give you the current fiscal week, month, quarter, year.
If constructed like that then any of those will be a well behaved dimension. You can always set the sort to the underlying date field.
One thing that did jump out at me is your use of WeekStart and WeekEnd. Those are timestamp values which make terrible record keys. Use Floor() on both of those to minimize any wierdness.
hth
-ws