Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Highlighted
Not applicable

How to avoid the summing up of values on Qlikview?


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

4 Replies

Re: How to avoid the summing up of values on Qlikview?

I see all your columns in Qlikview.

Could you elaborate your question?

MVP & Luminary
MVP & Luminary

Re: How to avoid the summing up of values on Qlikview?

I'm afraid I don't understand what you're trying to do. Can you post a qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
mov
Esteemed Contributor III

Re: How to avoid the summing up of values on Qlikview?

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.

Not applicable

Re: How to avoid the summing up of values on Qlikview?

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' 10Feb' 10Mar' 10Q1' 10
15161748
23242572
10203060


Case 2:

Jan' 10Feb' 10Mar' 10Q1' 10
00048
00072
00060

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