Calculating previous date data

[#1]

I am trying to determine a count of Issue ID of a previous Load Date.  The Load Dates are Thursday dates.  I have attached a file with the Load Date and the Issue IDs I am working with.  I do have duplicates in the source data, so I have been trying to use DISTINCT in the formula.

This is what I am trying so far and it is only giving me the current value:

=Num(count(Distinct{$<[Load Date]={">=$(=WeekStart(Addweeks(Today(),-1)))<=$(=WeekEnd(AddWeeks(Today())))"}>} [Issue ID]),'0')

The -1 Total should be the total from the previous week, but as you can see, it is the current total repeated.

My load statement for the Load Date is ensuring that the Load date is an actual Date:

LOAD Date(Num([Load Date]), 'MM/DD/YYYY') AS [Load Date],

Any help would be greatly appreciated!

[vishsaggi]

Your excel sheet has not got the field Total? Can you reattach the excel with your Total field values?

[#3]

Hi Vishwarath:

I am doing a DISCTINCT Count on Issue ID, which is Column A in the spread sheet.  The screen shot in the original post shows what the counts should be for each Load Date (827 at 12/15/2016, 836 at 12/22/2016, etc)

[vishsaggi]

Oh ok got it. But i doubt we have any function named AddWeeks here so the expression would not calculate any there? You just want to calculate previous week count?

[vishsaggi]

I am not sure let me know if this is what you are looking for ?

[#6]

Yes, that is exactly what I am looking for! How did you calculate that?

[vishsaggi]

I have just used these two expressions.

Dim: LoadDate

Exprs:

= Count(DISTINCT IssueID)

= Above(Count(Distinct IssueID))

[#8]

thank you so much!  One last question, if I don't want to show Load Date in the table, and just have the straight table that has current count Issue ID and Last Load Date Issue ID, how would I go about getting that?

[vishsaggi]

I did not get you. You want to hide the loaddate column and display only the count or you want to display IssueID as well?

To hide the load date try below.

[#10]

I am actually not wanting all of the Load Dates to show at all.  I need to have 1 Row, that ultimately shows current Load Date Issue Count , Previous Load Date Issue Count and I have an Arrow to show if Current Issue increased or decreased from last Load Date.