Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Formula In-Database Tool - sum(field1 + field2) / sum(field3)

rodalferreira
8 - Asteroid

Hi guys,

 

Relatively new with Alteryx Designer.

 

I'm trying to solve a problem translating a SQL Server query to an Alteryx Workflow and I'm having a issue with a mathematical expression.

 

 

SELECT field1,
       sum(field2 * field3)/ sum(field4) as Percentage
FROM table
GROUP BY field1

 

 

Any ideas how do I translate the second line of this code using in-DB formula tool?

 

Cheers,

 

 

2 REPLIES 2
vishwa_0308
11 - Bolide

Hi @rodalferreira,

 

1. First select formula tool and create a new field say field5 and use field2*field3.

2. Use summarize tool and do the following:

    a. Group By Field1

    b. Summarize Field 5

    c. summarize field 4

3. Again take a formula tool and create a new field and use:

   field5/field4

 

Thankss,

Vishwa

rodalferreira
8 - Asteroid

Thanks for your reply vishwa.

 

I've done exactly this, but the total of rows don't match. Before the formula I had 2133 rows. After the formulas I have 2177, probably a group by issue...

 

 

 

 

 

 

Labels