How to calculate first row/column after implementing after/before function?

[mrichman]

Hi Community,

Do you have any idea on how to approach this? I would like to select a range of dates but the first column should still calculate the difference of the "working day" before. 

I have a pivot pivot table with the following columns:

Week,

Date,

Accumalative Total,

Difference with day before.

The problem is, when I select week 26 for example, the column "Difference with day before" for the 25-06-2018 is always empty. This is because the the 24th hasn't been selected. Also when I select a range of date, 26-6-2018 to 27-06-2018, the column "Difference with day before" for the 26-06-2018 is empty with the same reason.

How can I best solve this?

I have tried to add the following formula =if(ColumnNo() = 1,'Y','T') but whenever i'm adding the calculations I get "-".

Thanks in advance!

[sunny_talwar]

First, what do you mean you are unable to select Week or Date in the table? The screenshot above shows the Date selected... week is a calculated dimension made up of date field (Week(Date)) that is why the current selection shows the Date when I selected Week 26.

Second, the other option and rather a better one would be to use The As-Of Table

[mrichman]

Hi Sunny,

Thank you for your message. I Have attached an attachment regarding the error.

Furthermore, the As-Of-Table was already recommended by you last time. However, i'm not known on how to implement it. Even after trying.

[sunny_talwar · Accepted solution]

Try this

(If(Match(Status, 'Level 1', 'Level 2'),

Count(DISTINCT ClientNumber),

Count(TOTAL <Team, Date> DISTINCT ClientNumber) - RangeSum(Above(Count(DISTINCT ClientNumber), 1, RowNo())))

-

Before(TOTAL

If(Match(Only({1}Status), 'Level 1', 'Level 2'),

Count({<Date>} DISTINCT ClientNumber),

Count({<Date>}TOTAL <Team, Date> DISTINCT ClientNumber) - RangeSum(Above(Count({<Date>}DISTINCT ClientNumber), 1, RowNo()))))) * Avg(1)

[mrichman]

Thanks for your help sunny! I had to change some things in the formula but I believe it's working properly now.