3 Replies Latest reply on Oct 19, 2018 5:17 AM by Ken Flerlage

    Subtracting using parameters on hour of date

    raoul.tller

      Hi,

       

      I am wondering if it is somehow possible to substract a X amount using paramaters for selected hours of every day in a month (11:00 AM and 14:00 / 2:00 PM).

      I have a balance coming in every 30minutes, and for this scenario I want to substract an X amount (parameter, for example 100000) of that balance? I have attached a data set.

      substract.png

        • 1. Re: Subtracting using parameters on hour of date
          Ken Flerlage

          This is definitely possible.

           

          1) First, you'll need to turn your Date field into an actual date (currently, it's a string).

          2) Second, create your parameter, which I've called Subtract.

          3) Create a calculated field, which I've called Balance Adjusted.

           

          // If the hour is 11 or 14, we want to subtract the amount from our total. Be sure to only include 11:00 and 14:00 exactly (not 11:30 or 14:30).

          IF (ATTR(DATEPART('hour', [Date]))=11 OR ATTR(DATEPART('hour', [Date]))=14) AND ATTR(DATEPART('minute', [Date]))=0 THEN

              SUM([Balance])-[Subtract]

          ELSE

              SUM([Balance])

          END

           

          4) Replace the Balance measure on your view with Balance Adjusted.

           

          See attached workbook.

          1 of 1 people found this helpful
          • 2. Re: Subtracting using parameters on hour of date
            raoul.tller

            Thanks Ken, I have been fiddling around with it a bit more, the end-user also wants to add a delay to the balance (30min, 60min, 90min, 120min), so I have created a parameter with:

            delay.png

             

            And then I work with this calculation: LOOKUP(ZN(SUM([Balance])), -[Delay]) and that will be Balance Previous.

             

            Which works. So now I wanted to combine the Subtract and Delay in one Balance:

            adjusted and delayed.png

             

            Which also works. But I want it to restart every DATE so at 00:00 all these modifications should restart (its a new day ).

            needs to restart every date.png

             

            restarting.png

             

             

            edit:

            I think I have figured it out, created a calc:

            [Code] + STR(DATE([Date]))

            Added that to the calculation, and restart every date, seems to do what I want.

            • 3. Re: Subtracting using parameters on hour of date
              Ken Flerlage

              Great. Glad you got it working!