Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
I am wondering how I can change this data so that the following data is displayed / visualized like the following example
Thanks
Barry
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
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 ...;
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
Thank you Jonathan,
This was much easier to set up and deploy.
Thank you Swuehi,
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 [
...