Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
I've attached a sample file that I'm currently working on. There are fields for Month, Quarter (Q1, Q2 etc), Half Year (H1, H2) and Year (FY). The data is present for all the months in few segments and in some segments, the data is present only in Quarter, Half Year and Year respectively. When I see this file on Qlikview, the monthly values are summed up for Quarter, Half year etc. Because of this, I'm unable to see the values for Quarter/ Half year/ Year for those segments where the monthly values are unavailable.
Can you please help me in resolving this issue? What should be done to see the values for Quarter/ Half Year and Year which are already present in the file even if the Monthly data is missing?
Thanks and Regards,
Arvind
I see all your columns in Qlikview.
Could you elaborate your question?
I'm afraid I don't understand what you're trying to do. Can you post a qlikview document that demonstrates the problem.
I think you should load only the Months data, and all the summing to be done in QV app. For the segments where there is no Monthly data, use some rule, for example assign to each month 1/3 of the Quarter amount.
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:
Case 1:
Jan' 10 | Feb' 10 | Mar' 10 | Q1' 10 |
15 | 16 | 17 | 48 |
23 | 24 | 25 | 72 |
10 | 20 | 30 | 60 |
Case 2:
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?
Tmp:
CrossTable(Period, Data, 7)
LOAD *
FROM
[Sample File 10th_Feb_2015.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Test:
LOAD*,
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;
Load*,
Date(MakeDate(TmpYear,Month(TmpMonth)),'YYYY-MM') as Date;
LOAD *,
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
Resident Tmp;
Drop Table Tmp;
Drop Fields TmpMonth,TmpYear;
Thanks again,
Arvind