7 Replies Latest reply on Apr 4, 2016 8:01 PM by Vijaya Pediredla

# How to show the count of today's date minus Last 14days

Hello All,

I need to count  of today's date minus last 14days. Here i have used, but no luck. Can someone tell me how to count.

WINDOW_SUM( COUNTD([Job Run Date]), FIRST(), 0) - TOTAL( COUNTD([Capability Objectid]))

For example:

CAPS i have today' count = 3

Last 14day's  = 1

I need to minus 3-1 =2.

Any idea.

• ###### 1. Re: How to show the count of today's date minus Last 14days

Hi Vijaya,

If you are in 9.0 or newer a much easier way to do this is with a LOD calculation in the example data set that comes with Tableau. Note this calculation goes back 14 days from the most recent day in the data set. It does not care if you are missing records for some day in between. The max date portion could be replaced by TODAY() if you wanted to use the current date.

Thanks,

Kent

• ###### 2. Re: How to show the count of today's date minus Last 14days

Hello Kent,

Still not good. I am looking for if April we have count of 3 and march 29

count of 1, i need to minus 3-1 = so i am expecting 2. Is there a way we

can do on window calculation using first and last. Please let me know.

On Fri, Apr 1, 2016 at 3:38 PM, Kent Sloan <tableaucommunity@tableau.com>

• ###### 3. Re: How to show the count of today's date minus Last 14days

Hi Vijaya,

Using the same logic with the date in the LOD example above this can be done. Just subtract the sum of these two calculations.

Today's Sales:

IF [Order Date]={MAX([Order Date])}

THEN [Sales] END

14 Days Back Sales:

THEN [Sales] END

• ###### 4. Re: How to show the count of today's date minus Last 14days

This could be done with a table calculation but it will be much more limited than the LOD version

You would have to have a sheet listing the values by day then use the following table calculation then use either a first or last calculation to hide all but the most recent day, which one of these you should use depends on how you have the dates sorted. The calculation below is used with the dates sorted in descending order. You may need to change the 13 to -13 if sorted ascending.

ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), 13)

Thanks,

Kent

• ###### 5. Re: How to show the count of today's date minus Last 14days

Thanks Kent, Still its not working. Actually, we are creating a snapshot of

each day.We should compare below scenario. Still no luck. If you have any

thoughts let me know. I have used the calculation but its counting 1.

For the capabilities/features added in past 2 weeks, it should compare the

count of capabilities of the ST today, and the same count of its baseline

(14 days ago).

IF COUNTD() < TOTAL(COUNTD())

AND MAX() = TOTAL(MAX())

THEN 0

ELSE 1

END

On Fri, Apr 1, 2016 at 4:24 PM, Kent Sloan <tableaucommunity@tableau.com>

• ###### 6. Re: How to show the count of today's date minus Last 14days

Hi Vijaya,

If you attached a data sample in a twbx I or someone else would be able to set up the calculation. Currently I am not sure what you would need based on your data source to make this work.

Thanks,

Kent

• ###### 7. Re: How to show the count of today's date minus Last 14days

Hello Kent,

Found the solution on the above issue. Here is the sol..

BeginCount:

WINDOW_MAX(

IF MAX([Capabilities Run Date]) = TOTAL(MIN([Capabilities Run Date]))

THEN COUNTD([Capability Objectid)

ELSE NULL END)

EndCount:

IF MAX([Capabilities Run Date]) = TOTAL(MAX([Capabilities Run Date]))

THEN COUNT([Capability Objectid (Polarisversioncontrol)])

ELSE NULL END

# of Capabilities within Last Two Weeks:

[EndingCountCAP]-  [BeginCountCAP]

Use the filter to show to count of each row:

IF MAX([Capabilities Run Date]) = TOTAL(MAX([Capabilities Run Date]))

THEN 1

ELSE 0 END

Thanks,

Vijaya