10 Replies Latest reply: Nov 22, 2011 1:27 AM by adiemunsch RSS

    Need advice on advanced Qlikview calculations and limitations

      Hello all,

       

      I refer you to this conversation http://community.qlik.com/message/164426#164426

      I have found no solution so far. A prerequisite to a solution existing in Qlikview is the following :

       

      Does Qlikview have an equivalent to the Excel formula arrays ?

      I want to return for each Work order (referenced by WONUM) how many WONUM were reported before (where the reported date is REPORTDATE)

      Not that here an aggregation is not possible since the date is a dimension and dynamic, so I have had no luck with set analysis.

       

      WONUMREPORTDATEWONUM created  before
      BC1425292011/08/030
      BC1427962011/08/071
      BC1428082011/08/082
      BC1435232011/08/203
      BC1435312011/08/214
      BC1436792011/08/245

       

       

      Where for instance C2 = {=SUM(IF((B2>B$1:B$7),1,0))} Formula array

      I can't seem to find how to do this in Qlikview.

       

      Please help on this thread or the other one.

       

      Thanks

       

      Arthur

        • No equivalent to Excel Formula array in Qlikview ??
          Dennis Hoogenboom

          I am not sure if it will work for you but I think you can use the Accumulation funtcion for this.

          Make a Count expression fe:

           

               Count (DISTINCT (REPORTDATE))

           

          And set the expression to Full Accumulation (left lower corner in the expression tab)

           

          Hope this is usefull.

           

          Good Luck,

          Dennis.

            • Re: No equivalent to Excel Formula array in Qlikview ??

              Hi Dennis and everybody


              Thanks for your answer. I guess this would work, however in my case there might be 2 or more identical REPORTDATE.

              Furthermore this is just a simplified version of what I need:

               

              Simplified (this thread). For each WONUM, return the count of WONUM that have a REPORTDATE less or equal to the REPORTDATE of the WONUM in question.

               

              Ultimately I need to calculate (refer to other thread, link above in first post):

              For each WONUM, return the max FINISHDATE of the subset of WONUM that have a REPORTDATE less or equal to the WONUM in question

               

               

              Hope you might have other ideas !

               

              Arthur

                • Re: Can't Qlikview do Formula arrays ??

                  hi

                   

                  not sure this is what you expect but look at th attached example

                   

                  Regards

                  Gilles

                    • Re: Can't Qlikview do Formula arrays ??
                      Deepak Vadithala

                      Hi,

                       

                      I agree with Giles. You can use Range expressions. Also, QlikView behaves completely different compared to Excel. Here is the brief overiew of QV data structures and how it works when we load the data.

                       

                      The data records are read into the memory, so that all the processing of data may be done through memory. I am sure you know this bit. QlikView treats all the data as Data Element Type (Columns / Fields) and Data Element Values (Values / Records). So each different data element value of each data element type is assigned a binary code and the data records are stored in binary-coded form and they are also sorted. By using the binary coding, very quick searches can be done on the tables. Also, QlikView removes the redundant information information and reduces the amount of data. However, the redundant information in stored as seperately with the frequencies for each unique data element value and across each data element type. When user makes a selection on data element values then the implied selection (possible values) are kept track seperately to present them to the user. By this process QlikView can perform rapid linear searches.

                       

                      I understand this is not in detail but just a quick overview. I hope this helps!

                       

                      Good luck!

                       

                      Cheers - DV

                        • Re: Can't Qlikview do Formula arrays ??

                          Hi guys, thanks so much for replying to my post.

                           

                          I understand the range functions,but the result should be independant of the sorting:

                           

                          For each WONUM, I want to return the number of WONUM which have a REPORTDATE less or equal to the REPORTDATE ofthis WONUM:

                           

                          Range functions work fine like this:


                          WONUMREPORTDATEWONUM reported  before
                          BC1425292011/08/030
                          BC1427962011/08/071
                          BC1428082011/08/082
                          BC1435232011/08/203
                          BC1435312011/08/214
                          BC1436792011/08/245

                           

                          but it should also work independently of the sorting (another/ no sorting):

                          WONUMREPORTDATEWONUM reported  beforeexplanation
                          BC1427962011/08/071BC142529 has a REPORDATE before 2011/08/07
                          BC1436792011/08/245BC142796, BC142808, BC142529,  BC143531, BC143523  have a REPORDATE  before 2011/08/24
                          BC1428082011/08/082BC142796, BC143523  have a  REPORDATE before 2011/08/08
                          BC1425292011/08/030no WONUM have a REPORDATE before 2011/08/03
                          BC1435312011/08/214BC142796, BC142808, BC142529, BC143523   have a REPORDATE before 2011/08/21
                          BC1435232011/08/203BC142796, BC142808, BC142529,   have a REPORDATE before 2011/08/20

                           

                          I hope this is clearer.

                          Ultimately (link in first post),I need to return, for every WONUM, the max FINISHDATE for the WONUM that have a REPORDATE before or equal to the REPORTDATE of that WONUM.

                           

                          Granted Excel works differently, I was referring to array formulas in Excel if someone is familiar with them and would understand what I am trying to code e.g. C2 = {=SUM(IF((B2>B$1:B$7),1,0))}

                           

                           

                          Thanks a lot !