7 Replies Latest reply: Oct 7, 2016 2:25 PM by hoang vo RSS

    Help with Set Analysis Expression

    hoang vo

      Hi, How would i go about writing an expression for : The sum of total, where the DateField is equal to the max of DateField and where DateField is Less than or Equal to the SelectedDate Thanks Hoang.

        • Re: Help with Set Analysis Expression
          Sunny Talwar

          Are these two separate conditions for two separate expressions? or needed in one expression? The first and second condition seems to be contradicting, isn't it? Can you share an example?

            • Re: Help with Set Analysis Expression
              hoang vo

              inv:

              load * inline

              [id,total,date

              1,10,2016-01-01 12:01:01

              2,10,2016-01-01 12:01:01

              3,10,2016-01-01 12:01:01

              1,5,2016-03-01 15:01:01

              3,15,2016-03-01 12:01:01

              4,10,2016-04-01 12:01:01

              1,15,2016-04-01 12:01:01

              ];

               

              3 island table containing just 5 year, 12month and 31 day .

              those are use for filter panel so i can select them:

              If i  chose year : 2016, month, 3, day 31

              I would have 2016-03-31 which i only want :

              id,total,date

              2,10,2016-01-01 12:01:01

              1,5,2016-03-01 15:01:01

              3,15,2016-03-01 12:01:01

               

              as the result table.

              the max date based on the selected panel when attempting to do this:

              max(if(date<=MakeDate(year,month,day),date))

              this gives me the max date exactly above, but how do i use this date to pull just the total out of the original table

            • Re: Help with Set Analysis Expression
              nagarjuna k

              Try this :


              Sum({<Date={"<=$(=vDate)"}>} Sales)



              vDate = "=max(date)"



              i hope this may helps you.



              -Nagarjun

                • Re: Help with Set Analysis Expression
                  hoang vo

                  That will not work because it will give me the total of max date. I just want the total depending on the date that is selected in the 3 island (unassociated table). The whole idea was that I would select a 'date' , which then would give me all entries prior or equal to the selected date, and then in that subset , i want to only display the max date, and all fields in that max date.

                • Re: Help with Set Analysis Expression
                  hoang vo

                  I have this resolved using FirstSortValue firstsortedvalue({$<RowID={"=If($(MaxDate)=Date,RowID)"}>}[Total],-Date)

                   

                   

                  I added RowID for each row as the RowNo() during load and got the desire result

                  Thanks