4 Replies Latest reply: Feb 23, 2015 12:11 AM by Arvind Sharma RSS

    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

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

          I see all your columns in Qlikview.

          Could you elaborate your question?

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

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

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

              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.

              • 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