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

# Subtracting using parameters on hour of date

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.

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

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

See attached workbook.

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

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:

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:

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

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

Great. Glad you got it working!