Visualization Question

[#1]

Good Morning,

I am bringing in sales data from my ERP system and the data is coming in great. The only problem I have is that for Period Sales it is displayed / visualized by the period number,

For Example:

• 1
• 2
• 3
• 4
• 5
• ETC

I am wondering how I can change this data so that the following data is displayed / visualized like the following example

• 1 = October
• 2 = November
• 3 = December
• etc

Thanks

Barry

[swuehl]

You can use a MAPPING LOAD for this kind of requirements:

PeriodMap:

MAPPING LOAD * INLINE [

Period, Name

1, October

2, November

3, December

4, ...

];

(Continue as requested)

ERPTable:

LOAD

     [Period Sales],

     APPLYMAP('PeriodMap',[Period Sales]) as [Period Sales Name],

     ...

FROM ...;

[jonathandienst · Accepted solution]

There are several ways of doing this. The best is to have a master calendar and the fiscal Period would be a field in the calendar.

If that is too complex for the moment, you can use a mapping

MapPeriod:

Mapping LOAD

Period, Dual(ApplyMap('MapPeriod', Period), Period) As Month

Inline

[

  Period, Month

  1, Oct

  2, Nov

  3, Dec

  4, Jan

  5, Feb

  6, Mar

  7, Apr

  8, May

  9, Jun

  10, Jul

  11, Aug

  12, Sep

];

Data:

LOAD ....

  Period,

  ApplyMap('MapPeriod', Period) As Month

  ....

The code Dual(ApplyMap('MapPeriod', Period), Period) As Month ensures that you are creating dual month values that will sort correctly

[#4]

Thank you Jonathan,

This was much easier to set up and deploy.

[#5]

Thank you Swuehi,

[swuehl]

Hi Jonathan,

MapPeriod:

Mapping LOAD

Period, Dual(ApplyMap('MapPeriod', Period), Period) As Month

[...]

The code Dual(ApplyMap('MapPeriod', Period), Period) As Month ensures that you are creating dual month values that will sort correctly

I was not aware that you can call the Mapping table from within the Map definition. Is this really possible?

(It doesn't work at my side)

Or is this a typo and you are using:

MapPeriod:

MAPPING LOAD

Period, Dual(Month, Period) as Month

INLINE [

...