5 Replies Latest reply on Jun 24, 2018 9:29 PM by Haan Haan

# Show data for max date in dataset with comparisons to previous dates

Hello,

I have a dataset that looks like this

DateSales
7/28/16\$100
7/29/16\$125
7/30/16\$85
7/31/16\$70

What I want to show is data for max date in data set i.e. 7/31/16 with comparisons to previous dates

Date                    Sales          Sales Difference vs Prev Day

7/31/2016            \$70               -\$15

The date needs to automatically advance every day to the most recent date.

How do I achieve this ?

• ###### 1. Re: Show data for max date in dataset with comparisons to previous dates

You can create a calc:

{ FIXED : MAX([Date]) }

THis will give you the biggest date in your data source.  (Or do an LOD using INCLUDE instead of FIXED to give you the highest date in the table in the sheet.)

You can use that all over the place.  Get difference from current row, calc all sorts of things with it.

1 of 1 people found this helpful
• ###### 2. Re: Show data for max date in dataset with comparisons to previous dates

Dear Joe

May I know how to get the differences from the current row?

Because the value of the max date is not tally with the max date....It provides the sum value of all days in the data source.

• ###### 3. Re: Show data for max date in dataset with comparisons to previous dates

To get the sales value for the max date use a calc:

If [Date] = [LOD MAXdate calc] then [sales] end

Then SUM([ ^ That Calc ^ ]) is the sales for that day.

And every row will have the max date in the [LOD MAXdate] calc.  So:

DATEDIFF('day',[date in this row],[LOD MAXdate])

this will tell you the number of days between the date in the current row and the max date.

1 of 1 people found this helpful
• ###### 4. Re: Show data for max date in dataset with comparisons to previous dates

Hi Doll,

I have used the different approach may be it will help you to get the view exactly like above

Step 1: Drag the Date Field to Rows and make it as Exact Date and Select Discrete

Step 2: Drag the Sales to Text

Step 3: Duplicate Sales field and name it as Sales Difference vs Prev Day and Drag to the worksheet where show me highlights

Step 4: You will get the view like below

Step 5: Click on the Sales Difference vs previous day Field and Select Difference in the Table Calculation

Step 6: Once you click it, you will get the difference of current row with the previous row like below

Step 7: Write a calculation like below and put into Filter, Select True

Step 8: You will get the required O/P

Whenever the maximum date comes into the picture it will automatically update

Hope this step by step solution helps, kindly mark this answer as helpful/Correct so that it will be helpful to others

1 of 1 people found this helpful
• ###### 5. Re: Show data for max date in dataset with comparisons to previous dates

Dear Naveen

But this method is not applicable to compare the date where previous date has no data...

May I know how to perform the calculation stated below:

For eg :

If  TODAY is 26/6, the differences shown should be (500-1000)/1000

Thanks