Hi,
I am have some problems to create a calculated field that returns the sum of another calculated field column. Here is an screen shot of my workbook. The filter I have applied is a table calculation which uses 12 months' data of the Call Date in Columns. I have hided the previous 11 months, and only showing the result of September 2022. The value inside of September 2022 is also a calculated field (Count) returns 1 if there is a result for each advisor in this month and 0 if there is not a record. Here is the screen shot of Count:
The filter grace period is a table calculation returns true or false, and I need it to only shows advisors with True.
I want a calculated field that would return the sum of Count for all advisors with True for the grace period, just like what is showed in the summary card that I have highlighted. So I can do some calculations in other workbooks. I am wondering if it is possible to achieve?
Thanks
@Zhenghao Zhang
The short answer: use a Level of Detail (LoD) calculation instead of table calculations. Below the LoD solution, I describe some of my attempt to apply the steps that would work based on the facts presented in the thread, but run into problems when applied to data (which is why it is always helpful to provide sample date when first asking the question). With either the parameter or scaffolding (see explanation below), table calculation can probably be made to work, but, at least with the file provided, the LoD method is easier.
First the row count LoD replacement
//Row score count-1 (LoD)
{ FIXED [Advisor name]:COUNTD(IF [Call Date]>[P_Call Date] THEN [Call Date] END)}
The Grace Period filter replacement
//C_Grace Period (LoD)
[Row score count-1 (LoD)]>0
Finally the count of advisors
//C_CountD Advisor
COUNTD(IF [C_Grace Period (LoD)] THEN [Advisor name] END)
//if you actually filter with [C_Grace Period (LoD)] you can just use
//COUNTD([Advisor name])
Table Calculation Explanation
I don't know enough about a grace period to comment on it, so I will accept that your calculation is accurate. Only Alan is excluded with the Grace Period calculation, leaving 3 advisors. From what I understand by going back and reading through post, you want that count of 3 to be returned.
The only differences between your calculation4 and the grace period calculation is that you are excluding advisors who are not in the grace period and you are no longer skipping the first two days in the driver attainment count. The count is actually irrelevant because you only care if the number is greater than 0 and you have already eliminated everyone who didn't meet that condition. Your calculation4 can thus be simplified to
//C_Count in Grace Period
window_sum(If [Grace period] then 1 ELSE 0 END)
//unless there is a reason to have ELSE, I generally exclude it so a NULL is returned
The table calculation setting will be different than for grace period. For grace period, you wanted the number of days per advisor. Therefore you computed (counted) using [Call Date] and restarted every [Advisor]. Now you want the number of advisors, so you will compute (sum) using [Advisors] and restart every [Call Date]. Every date has a value 1 assigned to it (or 0 for Alan), so every mark (date) will have the value 3 representing the three remaining advisors (technically 0+1+1+1).
Next we go from having a table to just having the single answer. All extra calculations are removed and required dimensions are moved to Details, but the values are now wrong. Instead of all threes, there is now one zero (0) and eight ones (1). The 0 is there because 'ELSE 0' is in the Grace Period count. If it had been left with just the implied ELSE, there would just be eight ones (1) and a NULL.
This is where table calculations can get (more) confusing. When both [Advisors] and [Call Dates] were on the shelves, each date appeared four times (once for each advisor). The WINDOW_SUM would sum the values of the four values on that one date and return it (the 0+1+1+1) mentioned earlier. When the calculation restarted every date, it went through seven (7) daily windows of four (4) values returning 3 for each of the 28 marks. With [Advisors] and [Call Dates] on the Detail mark, they no longer interact. Instead of seven windows of 4 values, there are nine (9) daily windows (two days are duplicated, the nested row score calculation forced them apart based on advisor, which is why there isn't 7 daily windows with two have the value of 2).
It is here that your realize you have a problem. Your Grace Period exclusion is a table calculation so using it as a filter will not help because it is evaluated last in the Order of Operations. It also depends on skipping the first two records. If the data were scaffold (every advisor has a record for every date), that would not be a problem, but advisors only have records for dates that have [Driver Attained] data. In this scenario, what is scaffolding doing that is resolving this issue? It is starting everyone's count on the same date so Alan is eliminated, but not James. Can the same thing be achieved without scaffolding? Yes, by using a parameter. A parameter has the added benefit of eliminating the need for table calculations. See the beginning of the reply for how.
