Reference the chart dimension in an expression

[#1]

Hi,

I have two tables, Account & Sales linked by AccountNumber:

Account:

LOAD * INLINE [

Date,Year,AccountNumber,AdjustmentType,Adjustment

2013-04-13,2013,100,Disc,15

2013-05-23,2013,104,Adv,35

2013-09-04,2013,107,Disc,18

2014-02-14,2014,100,Adv,30

2014-07-18,2014,104,Disc,15

2014-08-07,2014,107,Disc,15

];

Sales:

LOAD * INLINE [

SalesDate,SalesYear,AccountNumber,SalesPersonID,Value

2013-04-13,2013,100,09,100

2013-05-23,2013,104,09,85

2013-09-04,2013,107,14,35

2014-02-14,2014,100,09,40

2014-07-18,2014,104,09,25

2014-08-07,2014,107,14,75

];

The Straight Table is listing Adjustment sums by Year, and i want the Sales Value by the Year as well.

I know I can change the linking field to be AccountNumber & Year (AccountNumber & SalesYear in Sales), but for other reasons I want this to remain only by AccountNumber.

I want to reference the Dimension value in the expression for Value, i.e. SalesYear = Year.

I know i can manually evaluate each Year value against the SalesYear,

  if(only(Year) = 2013, sum({<SalesYear = {2013}>}Value),

       if(only(Year) = 2014, sum({<SalesYear = {2014}>}Value),sum(total Value)))

but i'd like the expression a bit more dynamic.

[nagaiank]

Load the tables using the script below and then create your charts

Account:

LOAD * INLINE [

Date,Year,AccountNumber,AdjustmentType,Adjustment

2013-04-13,2013,100,Disc,15

2013-05-23,2013,104,Adv,35

2013-09-04,2013,107,Disc,18

2014-02-14,2014,100,Adv,30

2014-07-18,2014,104,Disc,15

2014-08-07,2014,107,Disc,15

];

Concatenate (Account)

LOAD * INLINE [

Date,Year,AccountNumber,SalesPersonID,Value

2013-04-13,2013,100,09,100

2013-05-23,2013,104,09,85

2013-09-04,2013,107,14,35

2014-02-14,2014,100,09,40

2014-07-18,2014,104,09,25

2014-08-07,2014,107,14,75

];

[#3]

Hi Nagaian,

The example i provided was simplified, but i also need to be able to chart Account Table fields by Sales fields

(ex Sum(Adjustment) by SalesPersonID)

If the table is concatenated, the SalesPersonID will only apply to the Sales rows, so i can't get the result i need.

I still need the Sales Fields linked to Account by AccountNumber.

[MarcoWedel]

Hi,

I would advise to reconsider your restrictions regarding your link field. Using a combined Link ID would enable you to create a seperate calendar table with canonical dates. You then could create your specified charts without any fancy expressions:

Account:
LOAD *,
    AutoNumberHash128(AccountNumber, AccountDate) as %AccountDateID
INLINE [
AccountDate,AccountYear,AccountNumber,AdjustmentType,Adjustment
2013-04-13,2013,100,Disc,15
2013-05-23,2013,104,Adv,35
2013-09-04,2013,107,Disc,18
2014-02-14,2014,100,Adv,30
2014-07-18,2014,104,Disc,15
2014-08-07,2014,107,Disc,15
];

Sales:
LOAD *,
    AutoNumberHash128(AccountNumber, SalesDate) as %AccountDateID
INLINE [
SalesDate,SalesYear,AccountNumber,SalesPersonID,Value
2013-04-13,2013,100,09,100
2013-05-23,2013,104,09,85
2013-09-04,2013,107,14,35
2014-02-14,2014,100,09,40
2014-07-18,2014,104,09,25
2014-08-07,2014,107,14,75
];

tabLink:
LOAD Distinct
  %AccountDateID,
  AccountNumber,
  AccountDate as Date,
  'AccountDate' as DateType
Resident Account;

LOAD Distinct
  %AccountDateID,
  AccountNumber,
  SalesDate as Date,
  'SalesDate' as DateType
Resident Sales;

DROP Field AccountNumber From Account, Sales;

tabCalendar:
LOAD Distinct
  Date,
  Day(Date) as Day,
  WeekDay(Date) as WeekDay,
  WeekName(Date) as WeekName,
  Week(Date) as Week,
  WeekYear(Date) as WeekYear,
  MonthName(Date) as MonthName,
  Month(Date) as Month,
  QuarterName(Date) as QuarterName,
  Dual('Q'&(Month(QuarterStart(Date))+2)/3, Month(QuarterStart(Date))) as Quarter,
  Year(Date) as Year
Resident tabLink;

hope this helps

regards

Marco

[#5]

Sorry Marco,

I realized i set the dates in both tables the same, the Sales Table should have different dates, which would negate the link by Account Number and Date.

Account:

LOAD * INLINE [

Date,Year,AccountNumber,AdjustmentType,Adjustment

2013-04-13,2013,100,Disc,15

2013-05-23,2013,104,Adv,35

2013-09-04,2013,107,Disc,18

2014-02-14,2014,100,Adv,30

2014-07-18,2014,104,Disc,15

2014-08-07,2014,107,Disc,15

];

Sales:

LOAD * INLINE [

SalesDate,SalesYear,AccountNumber,SalesPersonID,Value

2013-04-17,2013,100,09,100

2013-06-18,2013,104,09,85

2013-09-29,2013,107,14,35

2014-04-02,2014,100,09,40

2014-08-11,2014,104,09,25

2014-10-13,2014,107,14,75

];

Any ideas with this revised data?