topic Crosstable to generate date while loading data in qlik in App Development

Crosstable to generate date while loading data in qlik

Aspiring_Developer — Sun, 23 Feb 2020 09:57:59 GMT

Hi Team,

I have the below data set :-

VLS Global

Customers	April-19	May-19	June-19	July-19	August-19	September-19	October-19	November-19	December-19	January-20	February-20	March-20
NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Unofficial Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)
Brazil	3	34	1	3	9,780	0	0	0	1	18,540	0	0	0	1	14,940	0	1	0	1	16,080	360	360	1	1	17,550	0	0	0	1	14,400	0	0	0	0	13,560	0	0	0	0	15,150	0	0	0	0	6,720	0	0	0	1	15,722	0	0	0	0	0	0	0	0	0	0
Bulgaria	0	0	2	2	66,300	0	0	0	0	66,247	0	0	1	1	58,524	2	2	2	2	31,413	0	0	0	0	42,678	0	0	0	0	55,928	0	0	0	0	56,031	0	0	0	0	67,833	1	3	1	1	32,529	92	192	3	3	1,21,395	0	0	0	0	0	0	0	0	0	0
India Pune	4	482	3	3	78,220	2	477	2	2	81,296	6	291	4	4	75,567	4	230	2	2	76,701	4	188	2	2	64,348	5	155	3	4	66,408	2	304	1	1	66,055	2	594	1	1	67,963	6	553	2	2	79,107	12	1,149	3	3	1,29,007	0	0	0	0	0	0	0	0	0	0
Italy	22	25	22	25	90,727	266	266	8	8	1,60,454	99	99	8	8	1,29,181	94	94	3	3	1,22,702	0	12	0	5	48,759	9	65	3	10	1,57,680	14	54	5	14	1,06,100	1	60	1	5	90,640	0	1	0	1	66,798	0	79	1	4	1,89,860	0	0	0	0	0	0	0	0	0	0
Mexico	60	1,991	2	9	3,98,365	6	1,216	5	18	5,03,689	6	956	2	11	4,41,184	2	875	2	9	3,97,019	1	398	1	19	3,82,425	2	217	0	15	4,08,460	26	355	6	18	4,26,588	1	255	1	9	4,04,869	2	151	2	8	3,12,553	12	152	2	5	3,52,511	0	0	0	0	0	0	0	0	0	0
Morocco	0	95	0	0	25,468	0	68	0	0	32,506	2	34	0	2	49,553	1	55	1	1	59,620	0	9	0	0	28,168	0	185	0	0	45,733	4	70	4	4	68,125	0	42	0	0	53,343	7	94	2	2	56,074	0	40	0	0	81,483	0	0	0	0	0	0	0	0	0	0
Novy Jicin	27	481	15	34	9,33,556	21	988	21	53	10,17,364	13	635	10	37	8,49,793	12	1,008	7	59	6,40,068	13	456	6	17	6,28,021	8	795	8	30	9,51,843	8	677	7	41	9,57,245	7	1,261	7	47	8,57,426	10	477	6	37	6,59,019	157	2,884	11	37	8,75,779	0	0	0	0	0	0	0	0	0	0
Rychvald	3	45	3	14	1,88,561	6	63	3	23	2,43,949	4	148	4	15	2,15,828	10	184	6	24	1,89,929	3	20	3	13	1,65,309	13	54	9	33	2,62,372	10	56	10	27	3,22,985	12	31	5	18	3,05,424	3	58	3	11	2,30,734	6	167	6	27	3,23,616	0	0	0	0	0	0	0	0	0	0
Romania	0	0	1	1	2,72,355	0	10	0	2	3,19,040	0	2	0	1	2,61,607	0	1	0	1	3,18,170	0	0	0	0	1,15,039	714	902	3	4	3,19,730	2	2	2	2	2,16,981	0	3	0	2	2,72,213	0	0	0	0	1,08,604	0	0	0	0	2,73,642	0	0	0	0	0	0	0	0	0	0
Poland	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	129	2	29	3,248	0	182	0	26	14,923	0	0	0	0	0	0	0	0	0	0
Turkey	6	6	4	4	21,49,175	362	387	1	2	23,23,812	6	6	4	4	17,69,565	319	319	4	6	19,77,009	0	178	1	1	17,91,943	18	300	2	8	26,33,696	8	132	4	6	21,84,676	28	443	6	17	26,43,949	16	27	4	6	22,88,836	6	17	3	4	23,99,265	0	0	0	0	0	0	0	0	0	0
Vietnam	1	1	1	1	78,699	107	209	7	7	72,513	30	30	2	2	86,870	2	2	2	2	79,711	4	4	3	3	78,405	1	1	1	1	84,290	1	1	1	1	70,895	195	195	1	1	87,071	7	7	2	3	87,395	1	1	1	1	1,14,358	0	0	0	0	0	0	0	0	0	0
VTYC CQ	3	8	3	3	1,50,985	0	0	0	0	86,839	7	174	4	7	1,51,333	4	9	2	4	1,60,170	6	114	3	9	1,03,708	0	0	0	0	1,88,943	2	31	2	31	2,23,373	8	8	4	4	2,61,951	2	2	2	2	2,28,203	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
VTYC CZ	5	5	2	2	1,02,486	0	4	0	0	1,09,470	1	3	1	3	84,792	3	9	4	9	97,609	2	4	2	2	1,00,146	0	2	0	2	1,17,626	0	1	0	1	1,05,194	1	1	1	1	1,47,849	1	17	1	6	94,689	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
India Chennai	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Europe (NJ,RY,PL, Mor.)	30	621	18	48	11,47,585	27	1,119	24	76	12,93,819	19	817	14	54	0	23	1,247	14	84	8,89,617	16	485	9	30	8,21,498	21	1,034	17	63	12,59,948	22	803	21	72	13,48,355	19	1,334	12	65	12,16,193	23	758	13	79	9,49,075	163	3,273	17	90	12,95,801	0	0	0	0	0	0	0	0	0	0
US (Brazil+ Mexico)	63	2,025	3	12	4,08,145	6	1,216	5	19	5,22,229	6	956	2	12	0	2	876	2	10	4,13,099	361	758	2	20	3,99,975	2	217	0	16	4,22,860	26	355	6	18	4,40,148	1	255	1	9	4,20,019	2	151	2	8	3,19,273	12	152	2	6	3,68,233	0	0	0	0	0	0	0	0	0	0
2WSL	29	35	30	33	26,57,256	735	872	16	19	29,42,066	135	137	15	16	0	417	418	11	14	25,29,005	4	194	4	9	20,76,824	742	1,268	9	23	32,51,324	25	189	12	23	26,34,683	224	701	8	25	31,61,706	24	38	7	11	25,84,162	99	289	8	12	30,98,520	0	0	0	0	0	0	0	0	0	0
China	8	13	5	5	2,70,858	0	4	0	0	1,96,309	8	177	5	10	0	7	18	6	13	2,57,779	8	118	5	11	2,03,854	0	2	0	2	3,06,569	2	32	2	32	3,28,567	9	9	5	5	4,09,800	3	19	3	8	3,22,892	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
India (Pune+ chennai)	4	482	3	3	78,220	2	477	2	2	81,296	6	291	4	4	0	4	230	2	2	76,701	4	188	2	2	64,348	5	155	3	4	66,408	2	304	1	1	66,055	2	594	1	1	67,963	6	553	2	2	79,107	12	1,149	3	3	1,29,007	0	0	0	0	0	0	0	0	0	0
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
TOTAL ACTUAL	134	3,173	59	101	45,44,677	770	3,688	47	116	50,35,719	174	2,378	40	96	41,88,737	453	2,789	35	123	41,66,201	393	1,743	22	72	35,66,499	770	2,676	29	108	53,07,109	77	1,683	42	146	48,17,808	255	2,893	27	105	52,75,681	58	1,519	27	108	42,54,509	286	4,863	30	111	48,91,561	0	0	0	0	0	0	0	0	0	0

I wish to get the below output:-

	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Unofficial Written concern
Apr-19	29	698	12982	22224
May-19	153	732	9333	23035
Jun-19	42	568	9549	22919
Jul-19	109	669	8401	29523
Aug-19	110	489	6169	20188
Sep-19	145	504	5464	20350
Oct-19	16	349	8718	30304
Nov-19	48	548	5118	19903
Dec-19	14	357	6346	25385
Jan-20	58	994	6133	22692
Feb-20	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Mar-20	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!

Can anyone please help ?

Thanks in advance

Re: Crosstable to generate date while loading data in qlik

jochem_zw — Mon, 24 Feb 2020 10:15:47 GMT

can you attach your data in XLSX format

Re: Crosstable to generate date while loading data in qlik

Aspiring_Developer — Mon, 24 Feb 2020 10:24:48 GMT

Hi,

Sure. I have attached the excel file.

Please consider the "Quality Perfo Dashboard-Yearly " tab for data. kindly ignore the rest of the tabs.

Re: Crosstable to generate date while loading data in qlik

jochem_zw — Mon, 24 Feb 2020 12:00:40 GMT

sorry but the excel is bit complex, and not easy as a quick thing for me to do. it should be possible to do, but i think you have to make some manual changes to xls.

Re: Crosstable to generate date while loading data in qlik

Aspiring_Developer — Tue, 03 Mar 2020 12:14:34 GMT

Hi Team,

I have solved it my self.

I requested the user to make a slight change in the headers name and named it as "NOK parts in customer database - total_Apr19", "Number of official Written concern_May19", "Total number Alert + Unofficial Written concern_June19","Volumes shipped (external sales only)_July19", etc.

1) Simply load the data in QV:-

Data:
LOAD F1 as Customer,

[NOK parts in customer database - total_Apr19],
[Real NOK parts manufactured - total_Apr19],
[Number of official Written concern_ Apr19],
[Total number Alert + Written concern_Apr19],
[Volumes shipped (external sales only)_Apr19],

[NOK parts in customer database - total_May19],
[Real NOK parts manufactured - total_May19],
[Number of official Written concern_May19],
[Total number Alert + Written concern_May19],
[Volumes shipped (external sales only)_May19],

[NOK parts in customer database - total_Jun19],
[Real NOK parts manufactured - total_Jun19],
[Number of official Written concern_Jun19],
[Total number Alert + Written concern_Jun19],
[Volumes shipped (external sales only)_Jun19],

[NOK parts in customer database - total_Jul19],
[Real NOK parts manufactured - total_Jul19],
[Number of official Written concern_Jul19],
[Total number Alert + Written concern_Jul19],
[Volumes shipped (external sales only)_Jul19],

[NOK parts in customer database - total_Aug19],
[Real NOK parts manufactured - total_Aug19],
[Number of official Written concern_Aug19],
[Total number Alert + Written concern_Aug19],
[Volumes shipped (external sales only)_Aug19],

[NOK parts in customer database - total_Sep19],
[Real NOK parts manufactured - total_Sep19],
[Number of official Written concern_Sep19],
[Total number Alert + Written concern_Sep19],
[Volumes shipped (external sales only)_Sep19],

[NOK parts in customer database - total_Oct19],
[Real NOK parts manufactured - total_Oct19],
[Number of official Written concern_Oct19],
[Total number Alert + Written concern_Oct19],
[Volumes shipped (external sales only)_Oct19],

[NOK parts in customer database - total_Nov19],
[Real NOK parts manufactured - total_Nov19],
[Number of official Written concern_Nov19],
[Total number Alert + Written concern_Nov19],
[Volumes shipped (external sales only)_Nov19],

[NOK parts in customer database - total_Dec19],
[Real NOK parts manufactured - total_Dec19],
[Number of official Written concern_Dec19],
[Total number Alert + Written concern_Dec19],
[Volumes shipped (external sales only)_Dec19],

[NOK parts in customer database - total_Jan20],
[Real NOK parts manufactured - total_Jan20],
[Number of official Written concern_Jan20],
[Total number Alert + Written concern_Jan20],
[Volumes shipped (external sales only)_Jan20],

[NOK parts in customer database - total_Feb20],
[Real NOK parts manufactured - total_Feb20],
[Number of official Written concern_Feb20],
[Total number Alert + Written concern_Feb20],
[Volumes shipped (external sales only)_Feb20],

[NOK parts in customer database - total_Mar20],
[Real NOK parts manufactured - total_Mar20],
[Number of official Written concern_Mar20],
[Total number Alert + Written concern_Mar20],
[Volumes shipped (external sales only)_Mar20]
FROM
[C:\Users\Vaishali\Documents\Work Varrooc\Dhiloj Qlik Report\OneDrive_2020-02-11\MOR and Quality Improvement Plan, FY2020\1. Global File\ZZ Global January 2019.xlsx]
(ooxml, embedded labels, header is 3 lines, table is [To be deleted -Vaishali])
where RowNo()<25;

2) Create Calculative Dimensions and store it into QVD:-

Temp:
Load
Customer,
//official Customer PPM

if( [Volumes shipped (external sales only)_Apr19]=0,'0',

([NOK parts in customer database - total_Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000
) as OfficialCustomerPPM_Apr19,

if([Volumes shipped (external sales only)_May19]=0,'0',

([NOK parts in customer database - total_May19]/[Volumes shipped (external sales only)_May19])*1000000
) as OfficialCustomerPPM_May19,

if([Volumes shipped (external sales only)_Jun19]=0,'0',

( [NOK parts in customer database - total_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000
) as OfficialCustomerPPM_Jun19,

if([Volumes shipped (external sales only)_Jul19]=0,'0',

( [NOK parts in customer database - total_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000
) as OfficialCustomerPPM_Jul19,

if([Volumes shipped (external sales only)_Aug19]=0,'0',

( [NOK parts in customer database - total_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000
) as OfficialCustomerPPM_Aug19,

if([Volumes shipped (external sales only)_Sep19]=0,'0',

( [NOK parts in customer database - total_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000
) as OfficialCustomerPPM_Sep19,

if([Volumes shipped (external sales only)_Oct19]=0,'0',

( [NOK parts in customer database - total_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000
) as OfficialCustomerPPM_Oct19,

if([Volumes shipped (external sales only)_Nov19]=0,'0',

( [NOK parts in customer database - total_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000
) as OfficialCustomerPPM_Nov19,

if([Volumes shipped (external sales only)_Dec19]=0,'0',

( [NOK parts in customer database - total_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000
) as OfficialCustomerPPM_Dec19,

if([Volumes shipped (external sales only)_Jan20]=0,'0',

( [NOK parts in customer database - total_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000
) as OfficialCustomerPPM_Jan20,

if([Volumes shipped (external sales only)_Feb20]=0,'0',

( [NOK parts in customer database - total_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000
) as OfficialCustomerPPM_Feb20,

if([Volumes shipped (external sales only)_Mar20]=0,'0',

( [NOK parts in customer database - total_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000
) as OfficialCustomerPPM_Mar20,

//Site PPM Performance
if([Volumes shipped (external sales only)_Apr19]=0,'0',

([Real NOK parts manufactured - total_Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000
) as SitePPMPerformance_Apr19,

if([Volumes shipped (external sales only)_May19]=0,'0',

([Real NOK parts manufactured - total_May19]/[Volumes shipped (external sales only)_May19])*1000000
) as SitePPMPerformance_May19,

if([Volumes shipped (external sales only)_Jun19]=0,'0',

([Real NOK parts manufactured - total_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000
) as SitePPMPerformance_Jun19,

if([Volumes shipped (external sales only)_Jul19]=0,'0',

([Real NOK parts manufactured - total_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000
) as SitePPMPerformance_Jul19,

if([Volumes shipped (external sales only)_Aug19]=0,'0',

([Real NOK parts manufactured - total_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000
) as SitePPMPerformance_Aug19,

if([Volumes shipped (external sales only)_Sep19]=0,'0',

([Real NOK parts manufactured - total_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000
) as SitePPMPerformance_Sep19,

if([Volumes shipped (external sales only)_Oct19]=0,'0',

([Real NOK parts manufactured - total_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000
) as Site_PPM_Performance_Oct19,

if([Volumes shipped (external sales only)_Nov19]=0,'0',

([Real NOK parts manufactured - total_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000
) as SitePPMPerformance_Nov19,

if([Volumes shipped (external sales only)_Dec19]=0,'0',

([Real NOK parts manufactured - total_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000
) as SitePPMPerformance_Dec19,

if([Volumes shipped (external sales only)_Jan20]=0,'0',

([Real NOK parts manufactured - total_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000
) as SitePPMPerformance_Jan20,

if([Volumes shipped (external sales only)_Feb20]=0,'0',

([Real NOK parts manufactured - total_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000
) as SitePPMPerformance_Feb20,

if([Volumes shipped (external sales only)_Mar20]=0,'0',

([Real NOK parts manufactured - total_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000
) as SitePPMPerformance_Mar20,

//Official Customer IPB

if([Volumes shipped (external sales only)_Apr19]=0,'0',

([Number of official Written concern_ Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000000
) as OfficialCustomerPB_Apr19,

if([Volumes shipped (external sales only)_May19]=0,'0',

([Number of official Written concern_May19]/[Volumes shipped (external sales only)_May19])*1000000000
) as OfficialCustomerIPB_May19,

if([Volumes shipped (external sales only)_Jun19]=0,'0',

([Number of official Written concern_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000000
) as OfficialCustomerIPB_Jun19,

if([Volumes shipped (external sales only)_Jul19]=0,'0',

([Number of official Written concern_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000000
) as OfficialCustomerIPB_Jul19,

if( [Volumes shipped (external sales only)_Aug19]=0,'0',

( [Number of official Written concern_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000000
) as OfficialCustomerIPB_Aug19,

if( [Volumes shipped (external sales only)_Sep19]=0,'0',

( [Number of official Written concern_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000000
) as Official_Customer_IPB_Sep19,

if( [Volumes shipped (external sales only)_Oct19]=0,'0',

( [Number of official Written concern_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000000
) as OfficialCustomerIPB_Oct19,

if( [Volumes shipped (external sales only)_Nov19]=0,'0',

( [Number of official Written concern_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000000
) as OfficialCustomerIPB_Nov19,

if( [Volumes shipped (external sales only)_Dec19]=0,'0',

( [Number of official Written concern_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000000
) as OfficialCustomerIPB_Dec19,

if( [Volumes shipped (external sales only)_Jan20]=0,'0',

( [Number of official Written concern_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000000
) as OfficialCustomerIPB_Jan20,

if( [Volumes shipped (external sales only)_Feb20]=0,'0',

( [Number of official Written concern_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000000
) as OfficialCustomerIPB_Feb20,

if( [Volumes shipped (external sales only)_Mar20]=0,'0',

( [Number of official Written concern_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000000
) as OfficialCustomerIPB_Mar20

,
//Site Performace IPB

if( [Volumes shipped (external sales only)_Apr19]=0,'0',

( [Total number Alert + Written concern_Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000000
) as SitePerformanceIPB_Apr19,

if( [Volumes shipped (external sales only)_May19]=0,'0',

( [Total number Alert + Written concern_May19]/[Volumes shipped (external sales only)_May19])*1000000000
) as SitePerformanceIPB_May19,

if( [Volumes shipped (external sales only)_Jun19]=0,'0',

( [Total number Alert + Written concern_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000000
) as SitePerformanceIPB_Jun19,

if( [Volumes shipped (external sales only)_Jul19]=0,'0',

( [Total number Alert + Written concern_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000000
) as SitePerformanceIPB_Jul19,

if( [Volumes shipped (external sales only)_Aug19]=0,'0',

( [Total number Alert + Written concern_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000000
) as SitePerformanceIPB_Aug19,

if( [Volumes shipped (external sales only)_Sep19]=0,'0',

( [Total number Alert + Written concern_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000000
) as SitePerformanceIPB_Sep19,

if( [Volumes shipped (external sales only)_Oct19]=0,'0',

( [Total number Alert + Written concern_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000000
) as SitePerformanceIPB_Oct19,

if( [Volumes shipped (external sales only)_Nov19]=0,'0',

( [Total number Alert + Written concern_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000000
) as SitePerformanceIPB_Nov19,

if( [Volumes shipped (external sales only)_Dec19]=0,'0',

( [Total number Alert + Written concern_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000000
) as SitePerformanceIPB_Dec19,

if( [Volumes shipped (external sales only)_Jan20]=0,'0',

( [Total number Alert + Written concern_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000000
) as SitePerformanceIPB_Jan20,

if( [Volumes shipped (external sales only)_Feb20]=0,'0',

( [Total number Alert + Written concern_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000000
) as SitePerformanceIPB_Feb20,

if( [Volumes shipped (external sales only)_Mar20]=0,'0',

( [Total number Alert + Written concern_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000000
) as SitePerformanceIPB_Mar20

Resident Data;
Drop table Data;
store * from Temp into [C:\Users\Vaishali\Documents\Work Varrooc\Dhiloj Qlik Report.qvd](qvd);
drop table Temp;
Exit Script;

3)Load the calculative dimension QVD created above and create a temp table to create date:-

New_Data:
CrossTable(Data, Values)
LOAD Customer,
OfficialCustomerPPM_Apr19,
OfficialCustomerPPM_May19,
OfficialCustomerPPM_Jun19,
OfficialCustomerPPM_Jul19,
OfficialCustomerPPM_Aug19,
OfficialCustomerPPM_Sep19,
OfficialCustomerPPM_Oct19,
OfficialCustomerPPM_Nov19,
OfficialCustomerPPM_Dec19,
OfficialCustomerPPM_Jan20,
OfficialCustomerPPM_Feb20,
OfficialCustomerPPM_Mar20,
SitePPMPerformance_Apr19,
SitePPMPerformance_May19,
SitePPMPerformance_Jun19,
SitePPMPerformance_Jul19,
SitePPMPerformance_Aug19,
SitePPMPerformance_Sep19,
Site_PPM_Performance_Oct19,
SitePPMPerformance_Nov19,
SitePPMPerformance_Dec19,
SitePPMPerformance_Jan20,
SitePPMPerformance_Feb20,
SitePPMPerformance_Mar20,
OfficialCustomerPB_Apr19,
OfficialCustomerIPB_May19,
OfficialCustomerIPB_Jun19,
OfficialCustomerIPB_Jul19,
OfficialCustomerIPB_Aug19,
Official_Customer_IPB_Sep19,
OfficialCustomerIPB_Oct19,
OfficialCustomerIPB_Nov19,
OfficialCustomerIPB_Dec19,
OfficialCustomerIPB_Jan20,
OfficialCustomerIPB_Feb20,
OfficialCustomerIPB_Mar20,
SitePerformanceIPB_Apr19,
SitePerformanceIPB_May19,
SitePerformanceIPB_Jun19,
SitePerformanceIPB_Jul19,
SitePerformanceIPB_Aug19,
SitePerformanceIPB_Sep19,
SitePerformanceIPB_Oct19,
SitePerformanceIPB_Nov19,
SitePerformanceIPB_Dec19,
SitePerformanceIPB_Jan20,
SitePerformanceIPB_Feb20,
SitePerformanceIPB_Mar20
FROM
[C:\Users\Vaishali\Documents\Work Varrooc\Dhiloj Qlik Report.qvd]
(qvd) where RecNo()=25;

Temp:
Load
Customer,
Data,
SubField(Data,'_',1) as Dimension,
SubField(Data, '_',-1) as Date,
num(Date(Date#(SubField(Data, '_',-1),'MMMYY'),'DD/MM/YYYY') )As New_Date,
Values
Resident New_Data;
Drop Table New_Data;

4) Front End Visualization-

->Create a table ,pass this as dimension-> Date(New_Date, ‘MMM-YY’)

->Create 4 measures like this :-

*Official Customer IPB= Sum({<Dimension={'OfficialCustomerIPB'}>}Values)

-*Official Customer PPM= Sum({<Dimension={'OfficialCustomerPPM'}>}Values)

*Site Perfromance IPB= Sum({<Dimension={'SitePerformanceIPB'}>}Values)

*Site Performance PPM= Sum({<Dimension={'SitePPMPerformance'}>}Values)