17 Replies Latest reply: Apr 27, 2018 2:23 PM by Jahanzeb Hashmi RSS

    Set Analysis Returning Incorrect Values

    Eric Tan

      Hi everyone,

       

      I am very new to the coding, and I have a weird phenomenon affecting my set analysis results.


      Essentially, I'm looking to only sum field values for only "Sep" dates. It performs its job to force all other months' values to zero but I'm seeing incorrect and even zero values being returned for selected September dates, resulting in incorrect summaries. Ideally, the rightmost column should correspond to sum(inwVolume).


      Would a kind and enlightened please advise on my error, many thanks in advance.


      Set Analysis Error.JPG

        • Re: Set Analysis Returning Incorrect Values
          Sunny Talwar

          Would you be able to share you qvf file to look into this?

          • Re: Set Analysis Returning Incorrect Values
            Sumit Kulkarni

            Hi Eric,

             

            If you are looking to only sum field values for "Sep" dates then you can also try below formula with 'IF' statement.

            I did try both ways with set analysis and If statement, which works for me without any problem.

            However, you can try with 'IF' statement and let me know the result.


            Sum(If(inwMonth='Sep.',inwVolume))

             

            Thank you!

             

            Best Regards,

            Sumit Kulkarni

              • Re: Set Analysis Returning Incorrect Values
                Eric Tan

                Hi Sumit,

                 

                Thank you,

                 

                I've tried your recommendation by incorporating "IF' and encapsulating with double quotes, yet it still doesn't return the correct values.

                 

                Set Analysis Error2.JPG

                • Re: Set Analysis Returning Incorrect Values
                  Lech Miszkiewicz

                  Hi Eric,

                  Although If statement can work, it is a better practice to use SetAnalysis for performacje reasons (IF statement = performance killer)

                   

                  To solve your problem it would be great to see your inwMonth column values. Also you use " " characters in your search string for Set Analysis, where i would use single quotes '  '. The other thing i see there is that you have Sep. with dot (.) after Sep value. Is this what you have in inwMonth column?

                   

                  Another thing you need to consider is your data model. How do you create your inwMonth column. Is this field created by autocalendar or you have created it by yourself? Is it text, integer or dual value.

                   

                  regards

                  Lech

                    • Re: Set Analysis Returning Incorrect Values
                      Eric Tan

                      Thanks Lech,

                       

                      I do indeed notice that the data tables take a much longer time to generate the results when I tried using the "IF" statement.

                       

                      I'm also using double quotes based on good habit advocated in this article Quotes in Set Analysis. I did have a go using the single quote, but it yielded the same incorrect outcome as with using double quotes.

                       

                      On the source inwDate values, this data is derived from a company systema and the values are stored in an excel xlsx format using the custom date formatting by Excel. And when I upload the data onto Qlik Sense, I actually passed the values through a calendar script (copied from a resource found in Qlik forum) and yes it seems that the MMM-YYYY format contains a "." after MMM.

                       

                      QuartersMap: 

                      MAPPING LOAD  

                      rowno() as Month, 

                      'Q' & Ceil (rowno()/3) as Quarter 

                      AUTOGENERATE (12); 

                       

                      Temp: 

                      Load 

                                     min(inwDate) as minDate, 

                                     max(inwDate) as maxDate 

                      Resident Inwards; 

                       

                      Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

                      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

                      DROP Table Temp; 

                       

                      TempinwCalendar: 

                      LOAD 

                                     $(varMinDate) + Iterno()-1 As Num, 

                                     Date($(varMinDate) + IterNo() - 1) as TempDate 

                                     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                      MstinwCalendar:

                      Load 

                                     TempDate AS inwDate, 

                                     week(TempDate) As inwWeek, 

                                     Year(TempDate) As inwYear, 

                                     Month(TempDate) As inwMonth, 

                                     YeartoDate(TempDate)*-1 as inwCurYTDFlag, 

                                     YeartoDate(TempDate,-1)*-1 as inwLastYTDFlag, 

                                     inyear(TempDate, Monthstart($(varMaxDate)),-1) as inwRC12, 

                                     date(monthstart(TempDate), 'MMM-YYYY') as inwMonthYear, 

                                     ApplyMap('QuartersMap', month(TempDate), Null()) as inwQuarter, 

                                     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as inwWeekYear

                      Resident TempinwCalendar 

                      Order By TempDate ASC; 

                      Drop Table TempinwCalendar;

                  • Re: Set Analysis Returning Incorrect Values
                    Eric Tan

                    Hi Stefan,

                     

                    I've created the inwMonth with a calendar script.

                     

                    MstinwCalendar:

                    Load 

                                   TempDate AS inwDate, 

                                   week(TempDate) As inwWeek, 

                                   Year(TempDate) As inwYear, 

                                   Month(TempDate) As inwMonth, 

                                   YeartoDate(TempDate)*-1 as inwCurYTDFlag, 

                                   YeartoDate(TempDate,-1)*-1 as inwLastYTDFlag, 

                                   inyear(TempDate, Monthstart($(varMaxDate)),-1) as inwRC12, 

                                   date(monthstart(TempDate), 'MMM-YYYY') as inwMonthYear, 

                                   ApplyMap('QuartersMap', month(TempDate), Null()) as inwQuarter, 

                                   Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as inwWeekYear

                    Resident TempinwCalendar 

                    Order By TempDate ASC; 

                    Drop Table TempinwCalendar;

                    • Re: Set Analysis Returning Incorrect Values
                      Jahanzeb Hashmi

                      try this

                      Sum({<inwMonth={'Sep'}>}inwVolume)