Best approach to sum columns

[TTK]

I have a rule below that it trying to add up what will be 15 rows from one of 31 columns i.e. 15 rows for every day of the month.  Users enter either a X, M or E in the field and I need a total for each day i.e. T1. The kind of works however can't see to get the .5 the below always return a 1 for each entry no matter what value is entered.  Keep thinking this must be a better way to do this. 

T1=IF([entry.R1D1]=X,1,IF([entry.R1D1]=M,.5,IF([entry.R1D1]=E,.5,0))) + IF([entry.R2D1]=X,1,IF([entry.R2D1]=M,.5,IF([entry.R2D1]=E,.5,0)))

Appreciate any help.

[Katerina Kovriga {Stiltsoft}]

Hi @TTK ,

We can suggest using the Table Filter and Charts for Confluence app. 

Wrap your table in the Table Transformer macro and then in the Table Filter macro.

Note that if your Confluence instance is based on Cloud, you'll need the Table Toolbox macro to nest several macros.

Go to the Table Transformer macro and create a custom SQL-query:

SELECT *,
CASE
WHEN T1.'Day 1' LIKE "x" THEN FORMATWIKI("{cell:align=right}" + "1" + "{cell}")
WHEN T1.'Day 1' LIKE "m" THEN FORMATWIKI("{cell:align=right}" + "0.5" + "{cell}")
ELSE FORMATWIKI("{cell:align=right}" + "1.5" + "{cell}")
END
AS 'Day 1 Count',
CASE
WHEN T1.'Day 2' LIKE "x" THEN FORMATWIKI("{cell:align=right}" + "1" + "{cell}")
WHEN T1.'Day 2' LIKE "m" THEN FORMATWIKI("{cell:align=right}" + "0.5" + "{cell}")
ELSE FORMATWIKI("{cell:align=right}" + "1.5" + "{cell}")
END
AS 'Day 2 Count',
CASE
WHEN T1.'Day 3' LIKE "x" THEN FORMATWIKI("{cell:align=right}" + "1" + "{cell}")
WHEN T1.'Day 3' LIKE "m" THEN FORMATWIKI("{cell:align=right}" + "0.5" + "{cell}")
ELSE FORMATWIKI("{cell:align=right}" + "1.5" + "{cell}")
END
AS 'Day 3 Count',
CASE
WHEN T1.'Day 4' LIKE "x" THEN FORMATWIKI("{cell:align=right}" + "1" + "{cell}")
WHEN T1.'Day 4' LIKE "m" THEN FORMATWIKI("{cell:align=right}" + "0.5" + "{cell}")
ELSE FORMATWIKI("{cell:align=right}" + "1.5" + "{cell}")
END
AS 'Day 4 Count',
CASE
WHEN T1.'Day 5' LIKE "x" THEN FORMATWIKI("{cell:align=right}" + "1" + "{cell}")
WHEN T1.'Day 5' LIKE "m" THEN FORMATWIKI("{cell:align=right}" + "0.5" + "{cell}")
ELSE FORMATWIKI("{cell:align=right}" + "1.5" + "{cell}")
END
AS 'Day 5 Count'
FROM T*

As you can see, we transformed our letters into numeric values:

Now we go to the Table Filter macro and count totals for the required columns:

And here is your result table in the view page mode:

Hope it helps your case.

[TTK]

Wow this is way beyond my current understanding, but looks like what I am trying to do except I want the totals to under Day 1 - Day 5 as you call it (I just have 1 -31 column headings). Not what to do since my form is on the Cloud?.  What is meant by your note and how would that change your example? ref: Note that if your Confluence instance is based on Cloud, you'll need the Table Toolbox macro to nest several macros.

Is there no way to simple total the column "Day 1" at the bottom of the column with formula similar to my example?

[Katerina Kovriga {Stiltsoft}]

The Table Toolbox macro allows you to wrap your table in the Table Transformer and then in the Table Filter macro - new Cloud editor doesn't support macro nesting. And the settings for the Transformer and Filter will be the same as in the example.

The difficulty in summing up your columns is that you use letters and want to transform them into numbers. 

To sum up numbers is very easy - you just wrap your table in the Table Filter macro and check the Count totals option.

What concerns standard Confluence tools, they don't support calculations in tables at all. So, you'll need an additional app (ours or something else from the Atlassian Marketplace).

[Katerina Kovriga {Stiltsoft}]

And about your formula - maybe you've been already using any app? Then paste its name here or create a support request to get help from the vendor.

If you just want to work with plain Confluence table as in Excel or smth, that won't do - calculations in tables are not available by default.

[TTK]

I'm having trouble understanding your answers.   What is FROM T* in your example refer too? Or is that the table name or something else.  Where is T1 in your example I think my difficulty is that I'm not sure of what your referring too or pointing too in your examples.

Also I am not sure how can I determine if I have Cloud version or not, how can I be sure?  Also your answer "...And the settings for the Transformer and Filter will be the same as in the example."  Should that have been "...will not be the same..."  Also your answer "...maybe you've been already using any app?..." What is any app refer to here?

PS in other ConfiForms I am able to use a ConfiForms Rules for Field Definition to sum fields together, I just wanted to use similar for this form but have smarts to change X to 1 and E and M to .5.

Thanks

[Katerina Kovriga {Stiltsoft}]

1. T* means any table, you can replace it by T1 (the first table) for this query
2. To understand the query clearly, it is better to be familiar with the SQL basics and Wiki markup, besides we have a lot of examples (use cases) in our documentation
3. The right version is that "And the settings for the Transformer and Filter will be the same as in the example" - if your hosting is Server/DC, your just copy my example. If your hosting type is Cloud, you use the Table Toolbox macro to nest the Transformer and Filter, but their settings will still be the same
4. The hosting type you use and the list of additional apps that are installed for your instance should be retrieved from your administrator. There is a hint that if your Confluence address is "your_company or smth similar".atlassian.net, then it is supposed to be Cloud
5. Your ConfiForms is also an app from the Atlassian Marketplace, follow the link and try to raise a support ticket - the app was created by a trusted vendor, I think they will help you with the issue

[Stiltsoft support]

And you may try our Table Spreadsheet macro and its Excel-like cell formulas to create more advanced calculations within your tables.

For other users who may come across this question - please check it out!