Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Best approach to sum columns

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.

1 answer

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.

Fri 1-1.png

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

Fri 1-2.png

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:

Fri 1-3.png

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

Fri 1-4.png

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

Fri 1-5.pngHope it helps your case.

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?

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).

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.

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

  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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Posted in Feedback & Announcements

Developer Day is next week – check out the agenda!

Hey there, community! Hope everyone is well and safe! ✨ The last few months have been busy – especially with the judging + picking of our Codegeist 2020 winners, the launch of Atlassian Ventures, a...

196 views 1 7
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you