topic Re: Subtotal by fields (maybe Aggr) in App Development

Subtotal by fields (maybe Aggr)

shane_spencer — Mon, 20 Jan 2025 15:47:06 GMT

I'm trying to get a % of "VRN Check" for each scenario. To do that I want a Count of Records by [Lock Local Period], [TDW Legal Entity Code] and [TDW Amz Reg Jurisiction] only (exluding the [VRN Check] field) so I can divide the Count of Records by that.

So in the below example the new column should be the 1359 for Dec-2024 ACCI AB for both the first 2 rows.

Re: Subtotal by fields (maybe Aggr)

Bhushan_Mahajan — Tue, 21 Jan 2025 04:41:40 GMT

@shane_spencer Aggr by Local period and use total function in measure

Re: Subtotal by fields (maybe Aggr)

shane_spencer — Tue, 21 Jan 2025 10:02:08 GMT

I'm getting closer but I have an issue where it is putting the count per scenario in the first row but not the second. If you look at the below example I have kind of got the results using Set Analysis (top visual) but have had to remove the [VRN Check] field and put the %ages over 3 columns, but what I want to do is have the layout in the bottom visual but as you can see the circled cell is blank when it also should be 62 like the row above (i.e. 26 + 26). Hopefully you can see the formula I used in the labels.

Aggr(Count([Lock Local Period]) ,[Lock Local Period],[TDW Legal Entity Code],[TDW Amz Reg Jurisdiction])

Re: Subtotal by fields (maybe Aggr)

Bhushan_Mahajan — Tue, 21 Jan 2025 11:14:58 GMT

@shane_spencer Better do it in backend. Refer below script.

ABC:
Load * Inline [
Period,Code,Juri,VRN,Count
Dec-24,A,B,Missing,1354
Dec-24,A,B,Incorrect,4
Jan-24,A,B,Missing,1176
Jan-24,A,B,Incorrect,8
];

left join
CDE:
Load Period, Sum(Count) as Total
Resident ABC
group by Period;
Exit Script;

Re: Subtotal by fields (maybe Aggr)

shane_spencer — Tue, 21 Jan 2025 11:27:09 GMT

Thanks but I'm looking to do this in the visuals if that is possible. Also it's not Grouping up by period but 3 fields [Lock Local Period],[TDW Legal Entity Code] and [TDW Amz Reg Jurisdiction].

Re: Subtotal by fields (maybe Aggr)

Bhushan_Mahajan — Tue, 21 Jan 2025 11:35:30 GMT

@shane_spencer I think for more than one identifier, it will not work in straight table. You need to work it out in backend.

Re: Subtotal by fields (maybe Aggr)

MatheusC — Tue, 21 Jan 2025 13:27:35 GMT

Hi, @shane_spencer

With the total<> qualifier, like this:

Count( Total <[Lock Local Period],[TDW Legal Entity Code],[TDW Amz Reg Jurisdiction]> [Lock Local Period])

- Regards, Matheus

Re: Subtotal by fields (maybe Aggr)

Bhushan_Mahajan — Tue, 21 Jan 2025 13:34:21 GMT

@MatheusC Yes. It is right option to tackle in front end. Great.

Re: Subtotal by fields (maybe Aggr)

shane_spencer — Tue, 21 Jan 2025 14:28:37 GMT

@MatheusC that does not give me the expected value. For example if you look at the highlighted it shows 1752 instead of 62

Update: if I select a value from [Lock Local Period] it gives me the right value but isn't a solution in itself.

Update2: I belatedly realised that the [Lock Local Period] was not the field from the data but a calculated field. That's the trouble with supporting something someone else has built!