5 Replies Latest reply on Jul 30, 2018 4:54 AM by Simon Runc

# Display Data if date is less than Certain Date

Hello,

I am trying to create a filter to limit the future unfinished data. So, I need your help with calculation: So logic would be:

If Today() < 6/1/2018 than display Data for Program Date < 6/1/2017 which sounds easy but when Today () hit 6/1/2018 then cycle restart and now need to compare the 6/1/2019 and display the data for program Data <6/1/2017 so on.

AJ

• ###### 1. Re: Display Data if date is less than Certain Date

Hi

1 the end date is

Then you use the "End Date" in your calculation similar to

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

2 of 2 people found this helpful
• ###### 2. Re: Display Data if date is less than Certain Date

Thanks a lot! this worked for me

• ###### 3. Re: Display Data if date is less than Certain Date

Thanks

Jim

1 of 1 people found this helpful
• ###### 4. Re: Display Data if date is less than Certain Date

My question is almost similar and tried the same logic, however, getting an error.

Here is my requirement.

I need to calculate Phase 1 based on Filing and Received date.

Phase1 = Invoices received within 6 months of filing. I have received date and invoice amount fields.

For this logic, i created a first formula

"FilingDate+6Months" = filing date+ 180 days

Now I am trying to create another formula

If ATTR([Received Date]) <= [FilingDate+6Months] then sum([InvoiceAmountt]) ELSE '0' END

For this, i am getting an error "Can not mix aggregate and non-aggregate argument with this function".

I am not sure if this is the right way to display data based on two different dates.

• ###### 5. Re: Display Data if date is less than Certain Date

hi Satish,

So there is a couple of issues with the formula...

If ATTR([Received Date]) <= [FilingDate+6Months] then sum([InvoiceAmountt]) ELSE '0' END

ATTR([Received Date]) is an aggregate calculation (as it's wrapped in the ATTR aggregation) but [FilingDate+6Months] isn't an aggregate

What will work best is dependent on your VizLoD, as aggregate calculations depend on the VizLoD, but assuming the VizLoD is such that ATTR([Received Date]) is OK, the you can also wrap the  [FilingDate+6Months] in an ATTR to make it an aggregate too.

The other problem, is that the ELSE return is a string (as the zero is wrapped in ''), whereas (I assume) SUM(InvoiceAmount) is a number, so you need to remove the quotes around the zero (we can't have 2 different return data types, depending on the condition.

Hope that makes sense.