Calculated field that will get the % total from a specific date to a year back

Question

DataTypes

AffectedDate: Date & Time

RecDate: Date & Time

I have 2 Data Sources i am pulling my data from.

Above is the Data that is being pulled into this report. The AffectedDate comes from the Data source Elliott_QtyOnHand2 and it controls how many years/months are displayed.

I have this calculated field and the amount that shows up is controlled by the RecDate, which is in the Elliott_IssueQty data source.

Right now the Prev Amount Calculated field changes when i put the RecDate in the Filter area because the RecDate dictates how much data is summed in the Sum(Amount) part of the Prev Amount calculated field.

Right now i can put the RecDate in the filter area to show me the sum(Amount) in the Prev Amount calculated field of everything between a range of dates, but i need this to be done on the fly, as each field will be different.

For example if the Affected Date is Jan, 2017 we want the Sum(Amount) in the Prev Amount Calculated field to only sum from Jan, 2016 to Jan, 2017, which again is calculated off of the RecDate

Feb, 2017 would sum(amount) between Feb, 2016 - Feb, 2017 and so on...

Siva Siva · Accepted Answer · 2018-02-28T11:14:04

Create a calculated field and write below code:

[Order Date] >= MAKEDATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) AND 
[Order Date] < MAKEDATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

Replace order date with date field in your database.

Place the calcualted field in filter shelf and then select true

Edit--------------------------------------------------------------------

Using filter doesn't help dynamic functionality of your requirement, instead you need to use parameter to achieve this:

Create a parameter select date and then select datatype as date, Here you can manually type values or select any database field using list radio button and display format should be month year from drop down.

Now crate a calculated field and write below code.

        [Order Date] >= MAKEDATE(YEAR([Parameter 1])-1,MONTH([Parameter 1]),1) 
AND 
[Order Date] < MAKEDATE(YEAR([Parameter 1]),MONTH([Parameter 1])+1,1)

    //Change order date to your **Rec Date**, if **Rec Date** is `date time` then convert t0 date format, else just replace the order date with **REc date**

Place the created calculated field on filter area and select true

Note: if you are using the datbase filed to populate the parameter values then if any new data additions to database you need to update the parameter manually or just follow method in link

if you need I can share workbook, drop a comment if you have any queries

Calculated field that will get the % total from a specific date to a year back

1 Answers