6 Replies Latest reply: May 31, 2017 2:40 AM by Tim Poismans RSS

    Sum based on date in set analysis

    Tim Poismans

      Hi all

       

      In request of a customer, I'm trying to make a table that lists an amount paid over the course of a few date ranges.
      The customer wants to see how much was paid after 1 month, 2 months, 3 months, 6 months,...

       

      I decided to focus on the 1 month. With this working, the rest should be a walk through the park.

       

      Consider the following example data:

             

      FilenrDate creationdate actionEnd of range dateCheck if action
      in range
      text1Amount
      GM5756610/01/201114/01/201110/02/2011Tpayment202,75
      GM5756810/01/20114/02/201110/02/2011Tpayment30,28
      GM5757010/01/201115/02/201110/02/2011Fpayment888,71
      GM5757210/01/201125/01/201110/02/2011Tpayment563,62
      GM5757310/01/201118/01/201110/02/2011Tpayment55,51
      GM5757310/01/201118/01/201110/02/2011Tpayment235,47
      GM5757510/01/201127/01/201110/02/2011Tpayment455,93
      GM5757810/01/201124/03/201110/02/2011Fpayment122,70
      GM5757910/01/201114/04/201110/02/2011Fpayment550,54
      GM5758010/01/20115/05/201110/02/2011Fpayment100,05
      GM5758010/01/20113/03/201110/02/2011Fpayment182,00
      GM5758010/01/20113/02/201110/02/2011Tpayment182,00
      GM5758010/01/20115/04/201110/02/2011Fpayment91,00
      GM5758010/01/201116/06/201110/02/2011Fpayment22,79
      GM5758510/01/201127/01/201110/02/2011Tpayment99,47
      GM5758610/01/201124/01/201110/02/2011Tpayment576,61
      GM5758610/01/201130/03/201110/02/2011Fpayment47,17
      GM5758610/01/20113/02/201110/02/2011Tpayment576,62

         

      In the data, we have the Date creation: the date the file was created and the start date of the range.

      End of range date: the end date of the range. This is simply Date creation + 1 month.

      date action: the field that needs to fall in the range.

       

      The field 'Check if action in range' gives a T(rue) or F(alse) if the field is in range.

       

      Now, lets take Filenr GM57586 for example

      The filenr occurs 3 times, of which two fall in the range. Now I want to make a list, for each Filenr, which list the total Amount that falls within that range. For Filenr. GM57586 this would be 576.61 + 576.62 = 1153.23



      I tried to achieve this with the following formula, which doesn't work:

      Sum({<text1={'payment'}, [date action]={"<=$(=date(AddMonths([Date creation]),1)))"}>}Amount)

       

      Example of the desired table:

        

      FilenrAmount 1M
      GM57566202,75
      GM5756830,28
      GM57570
      GM57572563,62
      GM57573290,98
      GM57575455,93
      GM57578
      GM57579
      GM57580182,00
      GM5758599,47
      GM575861153,23

       

       

      Thanks in advance and kind regards,

       

      Tim

        • Re: Sum based on date in set analysis
          Jonathan Dienst

          Your expression looks correct except for an misplaced closing parenthesis - change to:

          Sum({<text1={'payment'}, [date action]={"<=$(=date(AddMonths([Date creation]),1))"}>}Amount)

          Sum({<text1={'payment'}, [date action]={"<=$(=date(AddMonths([Date creation],1)))"}>}Amount)

                                                                                                                           ^^^^^



          (edited)

            • Re: Sum based on date in set analysis
              Jonathan Dienst

              The inner date() function may be redundant:

              Sum({<text1={'payment'}, [date action]={"<=$(=AddMonths([Date creation],1))"}>}Amount)

               

              These expressions assume that all the date fields are proper QV numeric date values, not strings.

                • Re: Sum based on date in set analysis
                  Tim Poismans

                  Yes, noticed the parenthesis and checked, was an error with copying and scrambling the data.

                  The fields are QV numeric date values, first thing I checked.

                  But the only thing I can imagine is that there's something wrong with the formatting of the date fields.

                  Going to double check it again.

                  • Re: Sum based on date in set analysis
                    Tim Poismans

                    Ok, update on the issue:

                     

                    I got it working, although not completely.

                     

                    Fiddled with the formula a bit, eventually ending up to just using Num instead of Date.

                     

                    Sum({<text1={'payment'}, date={"<=$(=Num(AddMonths([Date creation],1)))"}>}Amount)

                     

                    The only issue I have at the moment, is that it works when I select one value. When I want to show a list for all my files, the formula returns 0.00 across the board.

                     

                    Any advice on the matter?

                      • Re: Sum based on date in set analysis
                        Jonathan Dienst

                        When nothing is selected, then more than one [Date creation] value is possible, which means that Num(AddMonths([Date creation],1)) is null. You need an aggregation function (like min() or max()). For example:

                         

                        Sum({<text1={'payment'}, date={"<=$(=Num(AddMonths(Max([Date creation]),1)))"}>}Amount)


                        This will still track your selections, but will also work when nothing is selected.


                        However, if you need a row by row comparison of [date] and [Date creation], then the set expression will not work. The set expression is evaluated once for the chart, not row by row.. The options are then

                        • Sum(If()) - but this can be slow if the data set is large.
                        • Create a derived flag field in the load script where 1 means "date in window". Then you can use a set expression with the flag field in the front end.
                          • Re: Sum based on date in set analysis
                            Tim Poismans

                            Hey Jonathan,

                             

                            Even though there's only one [Date creation] per file, the formula is calculated on chart level, so it won't work when nothing is selected, okay.

                             

                            There's more than a million records, a Sum(If()) doesn't seem like it's the best solution.

                            Had hoped I wouldn't have to dive in the script as it is not my own. Though seems like that would be the best solution.

                             

                            Thank you, Jonathan.