4 Replies Latest reply: Dec 20, 2016 7:10 AM by Sunny Talwar RSS

    Interval on set analysis

    ben lim

      Hi guys,

       

      I have a set analysis with this expression

       

      sum({<Region = {'Asia'}>}Amount)

       

      I have an interval involved

       

      Interval(Date1-Date2,'mm') < 121

       

      Basically I need to calculate where the sum of amount for asia only where the difference between the two timestamp are < 121 mins

       

      I def. need to do it all in one expression. cant do it in dimension, is there any way that i can do it.

       

      Thanks

        • Re: Interval on set analysis
          Shraddha Gajare

          Calculate interval in script to create a field and then use that field in set analysis.

           

          sum({<Region = {'Asia'},IntervalField = {"<121"}>}Amount)

          • Re: Interval on set analysis
            Anil Babu

            You may try this, I hope you may have separate field for Interval in script

             

            Then, you can use this

             

            Load Region, Date1, Date2, Interval(Date1-Date,'mm') as DateDiff, Amount;

            Load Region, Date1, Date2, Amount from DataSource;

             

            sum({<Region = {'Asia'}, DateDiff = {"$(=Hour(DateDiff) < 121/(24*60))"}>} Amount)

            • Re: Interval on set analysis
              Marcus Sommer

              You could try:

               

              sum({<Region = {'Asia'}, Amount = {"=Interval(Date1-Date2,'mm')<121"} >} Amount)

              or

              if(Interval(Date1-Date2,'mm') < 121, sum({<Region = {'Asia'}>}Amount))

               

              whereby it might be useful to do calculation-step within the script like suggested within the other answers.

               

              - Marcus

              • Re: Interval on set analysis
                Sunny Talwar

                May be this:

                 

                Sum({<Region = {'Asia'}>} If(Date1-Date2 < MakeTime(2, 1), Amount))

                 

                or

                Create Date1-Date2 in script and then like this:

                 

                Date1-Date2 as IntervalField


                Sum({<Region = {'Asia'}, IntervalField = {$(=121/(24*60))}>} Amount)