7 Replies Latest reply on Apr 7, 2016 12:12 PM by Shiva Prakash Y V

# Calculate a Date Field  to count the last 7 days, last 30 days,..

Hi All,

My data is as shown in the attachment.

I need to calculate the average and the Output should be

Date         Profit

12/9/2013       3

12/8/2013       5

12/7/2013       2

12/6/2013       5

12/5/2013       7

12/4/2013       5

12/3/2013       7

Last 7 Days    4.8 (i.e. (3+5+2+5+7+5+7)/7)

Last 14 Days   5.1 (i.e. (3+5+2+5+7+5+7+4+7+4+7+8+5+3)/14)

Last 30 days   5.9 (i.e (3+5+2+5+7+5+7+4+7+4+7+8+5+3+6+8+9+4+8+9+4+8+9+4+5+3+5+7+8+9)/30

For this created a calculated field for the date as:

Order_Date<Today())) then Left(str(Order_Date),11)

elseif Order_Date>DATEADD('day', -14,Today()) and Order_Date<Today())) then "Last 14 days"

elseif Order_Date>DATEADD('day', -30,Today()) and Order_Date<Today())) then

"Last 30 days"

end

But this doesn't work properly.

Any help,

• ###### 1. Re: Calculate a Date Field  to count the last 7 days, last 30 days,..

There are multiple ways to achieve what you want.

In this case it's easier to just use the Aggregation (Average) built in to the "Aggregate Measures" functionality, rather than trying to build your own Calculated Fields in place.

Your real challenge is / was that you want to filter the same date column to four different depths (all dates, last 7 days, last 14 days, last 30 days) in place.

If your data had unique record ID's (say, transaction ID's) then it would be easier to build Calculated Fields to just count the records you want, but in the absence of that, 3 different filters really seems to require 3 different filters.

See if the attached Workbook is closer to what you want.

• ###### 2. Re: Calculate a Date Field  to count the last 7 days, last 30 days,..

Hi cyber,

Check the attached file. This may help !

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 3. Re: Calculate a Date Field  to count the last 7 days, last 30 days,..

I am using SQL Server db, created a datasource with multiple tables and need to get the output on single sheet as below,

I am missing 'Last 7 Days' because the Query is wrong and Last 14 days is not divided by 14 days, so the output is wrong.

• ###### 4. Re: Calculate a Date Field  to count the last 7 days, last 30 days,..

Hey Cyber,

Please attach a sample file(.twbx) of your database, so that we can understand your problem clearly. Without knowing your data its difficult to understand the issue.

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 5. Re: Re: Calculate a Date Field  to count the last 7 days, last 30 days,..

Hello Prashant,

Thanks,

• ###### 6. Re: Re: Re: Calculate a Date Field  to count the last 7 days, last 30 days,..

Hi Cyber,

I saw your data & I think without creating 2 sheets this is not doable.

Problem: - Within a single calculated field you can't achieve that because for a single value included in your condition you want to show 2 values & that is not possible.

IF [X]='a' then 'x' elseif [X]='a' then 'y' end

In above example, for single value 'a' you want two values x & y. Similar problem is in your calculation & I am unable to find a solution for that & from my point of view that is not doable but may be someone may look into this.

Solution: - You can create two sheets & add sheet to the dashboard. I don't think that there is an issue in this. Attached is the file which is having solution of this.

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 7. Re: Calculate a Date Field  to count the last 7 days, last 30 days,..

Hi Cyber is this thread answered ? Have you found a solution or a workaround ?

I have the same problem too.

Hoping to hear from you. Thanks,