Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to load the excel with Transpose. But, Unfortunately transpose is not working. Here, I want to calculate for my Bar Graph which is Dim as Months(Jan ....Dec) And i want to calculate each month of the sales data
If Jan then i need to create three expressions one for 16, 17 & 18
Please help me
You sample XLSX does not have the real labels in columns 1-8. You can use this script:
T_Production_data:
CrossTable(T_MonthYear, Sales, 😎
LOAD A as Plant,
B as [Aranda PTC],
C as Material,
D as Product_Name,
E as [Pr Glob Brand],
F as [Base Unit],
G as Type,
H as [Cal. year / month],
[01.2016],
[02.2016],
[03.2016],
[04.2016],
[05.2016],
[06.2016],
[07.2016],
[08.2016],
[09.2016],
[10.2016],
[11.2016],
[12.2016],
[01.2017],
[02.2017],
[03.2017],
[04.2017],
[05.2017],
[06.2017],
[07.2017],
[08.2017],
[09.2017],
[10.2017],
[11.2017],
[12.2017],
[01.2018],
[02.2018],
[03.2018],
[04.2018],
[05.2018],
[06.2018],
[07.2018],
[08.2018],
[09.2018],
[10.2018],
[11.2018],
[12.2018]
FROM
(ooxml, embedded labels, table is Y0ANALYSIS_PATTERN);
Production_data:
LOAD *,
Month(MonthYear) as [Production Month],
Year(MonthYear) as [Production Year]
;
LOAD *,
Date(Date#(T_MonthYear, 'MM.yyyy')) as MonthYear
Resident T_Production_data;
DROP Table T_Production_data;
If you want help with a load problem, I suggest that you upload sample source data as well. But it sounds like you might need a crosstable load rather than a transpose.
Jonathan, Thanks for your Reply
PFA.
Now, after that i want to show the data Dimension is MMM format and the expression should be 2016, 2017 & 2018 Data
Like I want to know each month how much sales they are spending for each year
- ANIL
You sample XLSX does not have the real labels in columns 1-8. You can use this script:
T_Production_data:
CrossTable(T_MonthYear, Sales, 😎
LOAD A as Plant,
B as [Aranda PTC],
C as Material,
D as Product_Name,
E as [Pr Glob Brand],
F as [Base Unit],
G as Type,
H as [Cal. year / month],
[01.2016],
[02.2016],
[03.2016],
[04.2016],
[05.2016],
[06.2016],
[07.2016],
[08.2016],
[09.2016],
[10.2016],
[11.2016],
[12.2016],
[01.2017],
[02.2017],
[03.2017],
[04.2017],
[05.2017],
[06.2017],
[07.2017],
[08.2017],
[09.2017],
[10.2017],
[11.2017],
[12.2017],
[01.2018],
[02.2018],
[03.2018],
[04.2018],
[05.2018],
[06.2018],
[07.2018],
[08.2018],
[09.2018],
[10.2018],
[11.2018],
[12.2018]
FROM
(ooxml, embedded labels, table is Y0ANALYSIS_PATTERN);
Production_data:
LOAD *,
Month(MonthYear) as [Production Month],
Year(MonthYear) as [Production Year]
;
LOAD *,
Date(Date#(T_MonthYear, 'MM.yyyy')) as MonthYear
Resident T_Production_data;
DROP Table T_Production_data;
Thanks,
I forget this
Date(Date#(T_MonthYear, 'MM.yyyy')) as MonthYear