Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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.
Hi Aswin,
Thanks for your message. I don't understand what you mean. Could you please elaborate.
Thanks in advance.
I was confused earlier. Now understood your query trying on that
Try this in your second expression
=before(TOTAL rangesum([Accumalative Total],0,0))
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.
Hi Richman,
Can you share your expected output ? I am not able to understand your requirement.
Br,
KC
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()))))
Hi Jyothish KC.
Thanks for your message. Here's the sample.
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.