Sorry for the delay in response everyone.
My question is, Once the file is loaded on qlikview, I see that the data for a particular quarter is not available if the monthly data is missing. For example, take 2 instances here:
Jan' 10 Feb' 10 Mar' 10 Q1' 10 15 16 17 48 23 24 25 72 10 20 30 60
Jan' 10 Feb' 10 Mar' 10 Q1' 10 0 0 0 48 0 0 0 72 0 0 0 60
The file I have attached has the combination of both these cases. When I upload the data on qlikview and make selections, I see that the quarter values are seen only when the monthly values are available (As shown in case 1). But when the values are not present or has 0 in the monthly data, then even the quarter is showing as 0, where as in the excel file, that data is present (Case 2).
As Michael mentioned, I can split the values present in quarter by 3 and put them in months, but that doesn't help as I'm not supposed to change anything from the actual file.
Please let me know if my question is still not clear. I've used the below script to upload the data on qlikvew. Should it be modified or something?
CrossTable(Period, Data, 7)
[Sample File 10th_Feb_2015.xlsx]
(ooxml, embedded labels, table is Sheet1);
Year(Date) As Year,
Month(Date) As Month,
If(Len('Q'&Ceil(Month(Date)/3))>1,'Q'& Ceil(Month(Date)/3)) As Quarter,
If(Len('H'&Ceil(Month(Date)/6))>1,'H'& Ceil(Month(Date)/6)) AS HalfYear;
Date(MakeDate(TmpYear,Month(TmpMonth)),'YYYY-MM') as Date;
20& Right(Period,2) As TmpYear,
Date#(If(Not Period like 'Q*' And Not Period like 'H*' And Not Period like 'FY*' ,Left(Period,3)),'MMM') As TmpMonth
Drop Table Tmp;
Drop Fields TmpMonth,TmpYear;