combine values in new table and append to original

[Mikis]

Hello everyone,

Sorry for the cryptic subject, but I couldn't come up with something more descriptive.

I have a macro called 'addTotals' (cf attachment).

The input is a dataset (variable number of fields), the fields in that dataset for which we want totals (variable number of records) and the name that the total should have.

What it does is, for a number of specified columns, append the dataset again but with value 'total'.

Let's say my data is the following:

fieldA	fieldB	fieldC	value
A	C	E	0,15
B	D	F	0,25

and let's say I need totals for 'fieldA' and 'fieldB', then the macro will create the following:

fieldA	fieldB	fieldC	value
Total	C	E	0,15
Total	D	F	0,25
A	Total	E	0,15
B	Total	F	0,25

This works fine, but I also need the combined 'Totals'.

So the part that I'm missing is

fieldA	fieldB	fieldC	value
Total	C	E	0,15
Total	D	F	0,25
A	Total	E	0,15
B	Total	F	0,25
Total	Total	E	0,15
Total	Total	F	0,25

Does anyone know how to add this in a flexible way?

I know that the field will have a certain name (in this case 'Total') because it's one of the 3 macro inputs.

The number of fields for which I want totals is not fixed, nor is the total number of input fields.

Thanks a bunch,

Mikis

[MarqueeCrew]

@Mikis,

Have you tried using the "Add Totals" macro from the CReW macros?

Cheers,

Mark

[Mikis]

Hello MarqueeCrew,

Thanks for your suggestion.

I did look into this macro, but unless i'm mistaken it's not what I'm looking for.

The totals macro (correct me when wrong) adds a calculated row and/or column 'total'.

I don't want calculated rows, I want my original dataset but then appended with that same dataset where some fields have their values replaced by a 'total' (so it would double if there's one field to total, and more than tripple when there are 2 fields to total).

Perhaps the following example makes it more tangible.

Let's say I have a field 'physician_ID'.

A physician can be (just as an example) be specialized in cardiology or oncology, but I also want to make calculations ignoring this specific dimension ('overall specialty'). He can be active in France or Italy, but I also want to make calculations ignoring that dimension ('overall region')

Since the calculations will happen later on in the process, I don't want an aggregated field just yet. First I want to list all options, which is what I'm trying to do here.

Regards,

Mikis

[ponraj]

Here is the dynamic workflow you are looking for... 

Sample DatafieldToTotalWorkflowResults

[Mikis]

@ponraj

Could you please attach again? It says there's an error opening the file.

Data at the root level is invalid. Line 1, position 1.

From the looks of it, it seems that it's indeed what I need

[ponraj · Accepted solution]

Here you go

[Mikis]

Brilliant stuff, thanks a lot!