Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a problem that the data contains the fields ID,Year,1,2,3,4,5..,16 and my scenario is that I need to combine 12,13,14,15,16 column to one one column 12 and rename those 12 columns to Jan,Feb,Mar..Dec. And I need a new field in the front end of YTD sales too.
I have tried one please correct me if there is any error? If you want see the data set look into the file below in it Sheet 5 is the data set
data:
LOAD
ID,
"Year",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10",
"11",
"12",
"13",
"14",
"15",
"16",
RangeSum("12","13","14","15","16") as "12_new"
FROM [lib://Desktop/sample.xlsx]
(ooxml, embedded labels, table is Sheet5);
Drop Fields "12","13","14","15","16";
Rename Field "12_new" to "12";
data2:
CrossTable("Month",Sales,2)
Load
*
Resident data;
map:
Mapping
LOAD * Inline [
Month,MonthN
1,Jan
2,Feb
3,Mar
4,Apr
5,May
6,Jun
7,Jul
8,Aug
9,Sep
10,Oct
11,Nov
12,Dec
](delimiter is ',');
data3:
Load
ApplyMap('map',"Month") as "MonthN",
*
Resident data2;
Drop Tables data,data2;
Thanks in advance,
Hello,
Why you using Rangsum and Sum
This can be made more easily
Something like :
Data_temp:
CrossTable(MonthNum, Données, 2)
LOAD ID,
Year,
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
"12"+"13"+"14"+"15"+"16" as "12"
FROM [lib://Desktop/sample.xlsx]
(ooxml, embedded labels, table is Sheet5);
NoConcatenate
Data:
Load
*,
Pick(Num#(MonthNum), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') as Month_label
Resident Data_temp;
Drop table Data_temp;
With expression :
Sum({$<Year={$(=max(Year))}>}( [Data] ))
and if you want to ignore months selection use this :
Sum({$<Year={$(=max(Year))},Month_label=>}( [Data] ))
Advice : try to use in the future a master calendar to manage dates, it's very helpful
https://community.qlik.com/t5/QlikView-Scripting/Creating-A-Master-Calendar/td-p/341286