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!

[raajaswin]

First Expression you have used some if and range sum. when we select some day the result of your expression and the result of just count (distinct ) is same.

[mrichman]

Hi Aswin,

Thanks for your message. I don't understand what you mean. Could you please elaborate.

Thanks in advance.

[raajaswin]

I was confused earlier. Now understood your query trying on that

[raajaswin]

Try this in your second expression

=before(TOTAL rangesum([Accumalative Total],0,0))

[mrichman]

Hi Aswin,

Thanks for your prompt reply. It is still not working.

If you select week 26, then 25-06-2018 is empty. It should also calculate the difference between 25-06-2018 the working day before, in this case 24-06-2018.

Thanks in advance.

[jyothish8807]

Hi Richman,

Can you share your expected output ? I am not able to understand your requirement.

Br,

KC

[sunny_talwar]

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()))))

[mrichman]

Hi Jyothish KC.

Thanks for your message. Here's the sample.

[mrichman]

Hi Sunny,

Thanks for the reply. Almost. The output is what I want, but now i'm unable to select the week or date in the pivot table.

Do you maybe have another solution?

Thanks in advance.