Indirect Set Analysis

[JustinDallas]

Hello Folks,

I have a datamodel where I have a SalesTable joined to a CalendarTable which is joined to a BudgetTable.  My goal is that I have a Barchart that shows the SalesAmount and the BudgetAmount side-by-side. 

I know this requires me to use a P() construct, but I'm not quite able to get anything working.   Right now I've got his lame duck of a statement which does nothing other than throw out a big fat 0

Sum({1<[Budget AccountId]=P({1<[AccountId]={[Budget AccountId]}>}[Budget AccountId])>} [Budget SaleAmount])

What I want would look something like this, with the Budget Sum in Orange.

Here is the dummy data that I am working struggling with.

BEGIN CODE

Sales:
LOAD *,
DayStart(MakeDate(SalesYear,
Month(Date#(SalesMonth, 'MM')))) as '%sales_date_key'
;
LOAD * Inline
[
AccountId, SaleAmount, SalesYear, SalesMonth,
'MEGACORP', 1102, '2019','01'
'MEGACORP', 1381, '2019','02'
'MEGACORP', 1233, '2019','03'
'MEGACORP', 1404, '2019','04'
'NETFLIX', 1336, '2019','01'
'NETFLIX', 1231, '2019','02'
'NETFLIX', 1977, '2019','03'
'NETFLIX', 1058, '2019','04'
]
;

Budget:
LOAD *,
DayStart(MakeDate(BudgetYear,
Month(Date#(BudgetMonth, 'MM')))) as '%budget_date_key',
;
LOAD * Inline
[
'Budget AccountId','Budget SaleAmount','BudgetYear','BudgetMonth',
'MEGACORP', 1102, '2019','01'
'MEGACORP', 1381, '2019','02'
'MEGACORP', 1233, '2019','03'
'MEGACORP', 1404, '2019','04'
'NETFLIX', 1336, '2019','01'
'NETFLIX', 1231, '2019','02'
'NETFLIX', 1977, '2019','03'
'NETFLIX', 1058, '2019','04'
]
;

MasterCalendar:
Load
DayStart(Floor(Num(TempDate))) AS %sales_date_key,
DayStart(Floor(Num(TempDate))) AS %budget_date_key,
Date(DayStart(TempDate)) AS CalDate,
Month(TempDate) As Month,
Dual(Year(TempDate), Year(TempDate)) AS Year
;
LOAD
date(mindate + IterNo()) AS TempDate,
mindate,
maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('%sales_date_key', recno()))-1 as mindate,
max(FieldValue('%sales_date_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%sales_date_key');

END CODE

Any help is greatly appreciated.

[Vegar]

Try this expression:

Sum({<[Budget AccountId]=P([AccountId]) >} [Budget SaleAmount])

Have you considered adjusting your datamodell? You could move the link for the budget from the calendar to Sales. At the same adjusting the link field from just period to period and account?

LoadDayStart(MakeDate(...) & '|' & Account number as %period_account 

LOAD *,
DayStart(MakeDate(SalesYear,
Month(Date#(SalesMonth, 'MM')))) as %date,

DayStart(MakeDate(SalesYear,
Month(Date#(SalesMonth, 'MM')))) & '|' & AccountId as %period_account ,

AccountId,

SaleAmount
;
LOAD * Inline
[
AccountId, SaleAmount, SalesYear, SalesMonth,
'MEGACORP', 1102, '2019','01'
'MEGACORP', 1381, '2019','02'
'MEGACORP', 1233, '2019','03'
'MEGACORP', 1404, '2019','04'
'NETFLIX', 1336, '2019','01'
'NETFLIX', 1231, '2019','02'
'NETFLIX', 1977, '2019','03'
'NETFLIX', 1058, '2019','04'
]
;

Budget:
LOAD 

DayStart(MakeDate(BudgetYear, Month(Date#(BudgetMonth, 'MM')))) &  '|' & [Budget AccountId] as %period_account ,

[Budget SaleAmount]

; 
LOAD * Inline
[
'Budget AccountId','Budget SaleAmount','BudgetYear','BudgetMonth',
'MEGACORP', 1102, '2019','01'
'MEGACORP', 1381, '2019','02'
'MEGACORP', 1233, '2019','03'
'MEGACORP', 1404, '2019','04'
'NETFLIX', 1336, '2019','01'
'NETFLIX', 1231, '2019','02'
'NETFLIX', 1977, '2019','03'
'NETFLIX', 1058, '2019','04'
]
;

MasterCalendar:
Load
DayStart(Floor(Num(TempDate))) AS %Date,
Dayname(TempDate) AS CalDate,
Month(TempDate) As Month,
Dual(Year(TempDate), Year(TempDate)) AS Year
;

LOAD ...

[Vegar]

Another approach is to concatenate the two transactions tables into one.

Sales:

LOAD * Inline
[
AccountId, Amount, Year, Month,
'MEGACORP', 1102, '2019','01'
'MEGACORP', 1381, '2019','02'
'MEGACORP', 1233, '2019','03'
'MEGACORP', 1404, '2019','04'
'NETFLIX', 1336, '2019','01'
'NETFLIX', 1231, '2019','02'
'NETFLIX', 1977, '2019','03'
'NETFLIX', 1058, '2019','04'
]
; 

Concatenate(Sales)

LOAD * Inline
[
 'AccountId','Budget Amount', 'Year', 'Month'
'MEGACORP', 1102, '2019','01'
'MEGACORP', 1381, '2019','02'
'MEGACORP', 1233, '2019','03'
'MEGACORP', 1404, '2019','04'
'NETFLIX', 1336, '2019','01'
'NETFLIX', 1231, '2019','02'
'NETFLIX', 1977, '2019','03'
'NETFLIX', 1058, '2019','04'
]
;

[JustinDallas]

Thanks for the help, I was hoping to avoid doing any datamodel changes if at all possible.  Mostly because I wanted to grow my skills and Indirect Set Analysis is useful even if not used a whole bunch.

[Vegar]

Did my expression do any help? It should work without changes to the datamodel.

Sum({<[Budget AccountId]=P([AccountId]) >} [Budget SaleAmount])