14 Replies Latest reply: May 25, 2012 1:03 PM by Jonathan Dienst RSS

    Yet another between dates SET question...

    Robert Svebeck

      Hi..

      I 've been browsing the forum for a solution to my simple question, but I have not found a working solution. Perhaps it is too simple...?

      What I am looking for is a solution where I have sales data and contract dates to be able to calculate a possible bonus. If I have transactions within the contract dates, a bonus shold be paid out.

      I have solved this with "if" expressions, but now I need it with a SET expression also.


      I have made an example with two small tables:

      Transactions:
      Date       Value Type
      2012-07-17 302   A
      2010-08-21 369   B
      2010-08-10 984   C
      2011-02-25 869   A
      2011-06-17 637   B
      ...etc

       


      Contracts:
      Type  ValidFrom  ValidTo
      A     2011-01-01 2011-12-31
      B     2012-01-01 2012-12-31
      C     2011-07-01 2012-07-01

       


      Now, I want to make a table chart showing two sums of the values for A,B and C.

      Sum A: "Total value"
      Sum B: "Total valid value"

      My problem here is the expression for Sum B.

      This works:

      sum(if(Date >= ValidFrom and Date <= ValidTo, Value))

       

       

      But I want to use SET Analysis, and this does not work:

      sum( {$ <Date = {">= $(=only(ValidFrom)) <= $(=only(ValidTo))"}>} Value)

       

       

      The reason seems to be that Qlikview ignors my dimensions in my chart (Dimension is Type)  so that only(ValidFrom) and only(ValidTo) fails.


      Once I get this to work, I also need to be able to select one or several months, or days in the report and it will calculate the values for that period which is also included in the range of valid dates.

       

      So If I select November 2012, only values from November 2012 and from type B will be displayed, since type A and C are not within their valid date range.

      Perhaps my explenation is not easy to understand, so I also made a attached example.

      Hope someone can help me. It seems simple, but ...

      //Robert

        • Re: Yet another between dates SET question...
          Felim Shanaghy

          I have literally just asked the same question in another thread, I am not certain that Set Analysis is going to be the right tool for us...

           

          I could get Only to work since it was specific, Min and Max to cover ranges, I have been ripping my hair out with...

          • Re: Yet another between dates SET question...

            Hi,

             

            Apparently, u can't do it.

             

            Look this document : http://community.qlik.com/servlet/JiveServlet/previewBody/1867-102-1-1858/QlikLearn-Set-Analysis-Presentation.pdf

             

            I had the same question before and solved it by "if" like u did. Some colleague answer the same.

             

            Regards.

            • Re: Yet another between dates SET question...

              Hi again,

               

              Did you delete your previous message ?

               

              I join the test.qvw with my research. AS you can see, the set analysis limite is cross when we work with to much dimension.

               

              Maybe concatenate date and type ? Hard way to solve problem ...

               

              If isn't suffisant ?

                • Yet another between dates SET question...
                  Robert Svebeck

                  Hi.

                   

                  Yes, I deleted my previous post which was about changing the datamodel as a optional solution request, since I realized that it will only solve the problem in my example - but in my real world actual data it will not be possible to change the datamodel, since I have not 3 types but 1000+ types, and one single transaction can belong to many types, so I came to the conclusion that solving this by changing the datamodel is not what I want, I need a solition with SET.

                   

                  I look at your contribution in the test.qvw and as I understand, none of the set expressions give the same numbers as the IF expression. The "Foreced Type" way is probably in the right direction, but I don't understand why that example shows numbers for type a and C, it should only display numbers for B?
                  However - the forced type solution will not be a approach for my "real data" since I can't write one expression for each type when I have thousands of Types.


                  ("Types" are actually contracts in my real world data.

                   

                  I will keep investigating for a solution. So lets keep posting here until we finally give up or finaly solve it! Thanks for your feedback!

                   

                  Regards, Robert

                    • Yet another between dates SET question...

                      Daisuke is right...

                      If you check the Set analysis white paper, it clear says that the Set Expression is calculated only once per chart. Not for each dimension. So although you are referencing the "Type" as a dimension, the Set expression will not evaluate relevant values of only(ValidFrom) in the modifier clause for each row of Type. It will just find that out once per chart.

                        • Yet another between dates SET question...
                          Robert Svebeck

                          I'm getting more and more confused...

                           

                          So what does this example, taken from that PDF mentioned above,
                          actually calulate then?

                           

                          Page "Modifiers - Examples"

                          sum({$<OrderDate = DeliveryDate>} Sales)

                          "Returns the sales for the current selection where OrderDate = DeliveryDate."

                          Because if DeliveryDate is not depending on the dimension, it could be all or any delivery date?

                          I must be misunderstanding something here....

                            • Yet another between dates SET question...
                              Robert Svebeck

                              Hmm, it must mean that this sum({$<OrderDate = DeliveryDate>} Sales) is not working in a table chart with dimensions, it will only work in dimensionless charts and textboxes etc then. I guess...

                              • Yet another between dates SET question...
                                Vijay Kumar

                                Why dont you do it at back end. You can use Intervalmacth to find candidates for bonus and front end only use simple expression.

                                 

                                Regards

                                Vijay

                                  • Yet another between dates SET question...
                                    Robert Svebeck

                                    Yes, in this example it would be posible.

                                     

                                    But the background is that I have a transaction table with sales data (customers, items, dates, values). Then there are contract tables where we specify when the contract is valid, for which items and for which customer ID's they are valid.


                                    The sales data consists of 40,0000,000 records, and there are around 4,000 contracts.  So one single sales data row can be a valid row for many many (sometimes all) contracts, and one single contract is often valid for 10,000 items and 10 customer ID's. A contract duration spans from beeing valid only one month up to several years. And above that, a customer ID can appear on several contracts...

                                     

                                    I could run interval match for a few contracts perhaps, but not for all contracts?  - I  think...or am I wrong?

                                    The goal is to get the total sales any given month for all valid contracts to be able to calculate a kickback bonus per customer based on that.

                                      • Re: Yet another between dates SET question...
                                        Jonathan Dienst

                                        Hi

                                         

                                        If each bonus calculation is not dependant on any front-end selections, I would strongly recommend doing these calculations in the load script. This calculation may be time consuming, but it is far better that the time be spent in the back-end, which should signiificantly the front-end, user experience.

                                         

                                        Operations like interval matching perform very well in the back end, and assuming you have a suitable PC or server, there should be no reason not to do all contracts. But only testing this will prove whether this is the right approach or not.

                                         

                                        Regards

                                        Jonathan

                                    • Re: Yet another between dates SET question...

                                      I'm getting more and more confused...

                                       

                                      So what does this example, taken from that PDF mentioned above,
                                      actually calulate then?

                                       

                                      Page "Modifiers - Examples"

                                       

                                       

                                       

                                       

                                      for the current selection ! In your case, we dont select anything. If my test.qvw, the table partially work when I select a Type.

                                       

                                      Problem is to "link" Type with "ValidFrom/To" Date without selection. And we can't cause Set Analysis work with a set of information.

                                       

                                      In a table, 1 line isn't egal to a set. So QlikView can't determined Date set for A, B, C. That why he calculated the sum(Value) for all Type in spit of each line.

                                        • Yet another between dates SET question...
                                          Robert Svebeck

                                          Yeah, but if the users don't make a correct selection, they won't get any useful data from this expression. We must also then make sure that they select data that limits out all variants of DeliveryDate. If the DeliveryDate field has more than one possible value, it will fail. And doing a {1} kind of set with this solution will surely fail.

                                           

                                          Anyhow, I think I am slowly beginning to understand how this works and I guess I must realize that my task is not solvable with SET.... afterall.... I was hoping for smart solutions with adhocvariables or sometihng like that, but as I understand it now, this will not work either. I must somehow go back to the drawingboard here...

                                           

                                          Best Regards, Robert

                                • Re: Yet another between dates SET question...
                                  but in my real world actual data it will not be possible to change the datamodel, since I have not 3 types but 1000+ types

                                   

                                  I was "worry" about that. Change Data model will be impossible cause of data quantities

                                   

                                  but I don't understand why that example shows numbers for type a and C, it should only display numbers for B?

                                   

                                  No cause set analysis are looking for min/max valid date for all type. not only for Type (per line).

                                   

                                  That why after, I did some test about forced Type without solution ...

                                   

                                  Regards, I keep one eye here !