-
Need help creating Formula to select Invoice Number when it Contains an "A"
I need help writing a Formula for an Aggregated Field in a Group by that will select the Latest Invoice that has an "A" in it if it exists. This example only has 1 Credit and Adjustment (C & A Invoice but I could have multiple to I need to take into consideration the Last Invoice Date as well) Below is my attempt but I…
-
Grand Total Beast Mode not calculating correctly
The beast mode below is not calculating correctly in the Grand Total Column. CASE WHEN SUM(IFNULL(`Billed Amount`,0)) = 0 THEN 0 ELSE (((SUM(IFNULL(ABS(`Billed Amount`),0))) - (SUM(IFNULL(`Actual Material Cost`,0)) + (SUM(IFNULL(`Actual Labor Cost`,0)))))) / SUM(IFNULL(`Billed Amount`,0)) END
-
BEAST MODE CALCULATION ISSUE
Using the Beast Mode below I am being forced to use the ABS function in order for the Beast mode to give me a calculation. If I remove the ABS Function I get the error "An issue has occurred during processing. We are unable to complete the request at this time". The issue I have is I need to remove the ABS (Absolute Value…
-
Sort Not working as Desired
I need this Table to Sort by the Sub Total in the Total Column. But the CustName is being forced into the SORTING Option because I have selected SubTotal Rows. But I need SUBTotals selected because Each Customer has Phases. How can I Sort by SUBTotal Totals and NOT CustName?
-
Need Column Header to show Day of week ie MONDAY, TUESDAY etc
I Need the Column STATUSDATE to show the day of week ie Monday, Tuesday Wed etc. instead of the DATE but can not figure out out to FORMAT.
-
Average Calculation less than Trend Points during time period
Why is my Average Calculation of 2.84 less than all of the Trend Points.
-
Column Total Greater than Sum of ROWS
Why is "# Houses" Column Total Greater than Sum of ROWS on "Closed Punch Tickets by Plumber" Card BEAST MODE for "# of Houses": COUNT(DISTINCT(CASE WHEN JT158_WTParent = 'Y' AND HdrParentItemCode = 'ZPUNCH' THEN SalesOrderNo END))
-
Column is Adding rather than giving Average
Why is AVG Punches Per House giving a total of Column and not Avg but PUNCH SCORE Column Total is giving AVG. PUNCH SCORE Beast Mode 10 * (COUNT(DISTINCT SalesOrderNo) / COUNT(DISTINCT sonum_wtnumber)) AVG Punches Per House Beast Mode (CASE WHEN HdrParentItemCode = 'ZPUNCH' AND JT158_WTParent = 'Y' THEN…
-
Beast Mode Column Sort Not working Correctly
The Following Beast Mode is reversing 1 & 2 & 4 & 5 for some reason. Only Field in the correct position is Material. I have checked spelling and case of field names.
-
Need Column Subtotal and Total to be average of % Amounts not Totals
I need the Subtotal and Totals of % Spent Columns to be averaged not Totaled fyi - transactionamt = JTD Cost (IFNULL(CASE WHEN SourceCode = 'Blank' THEN Filter Rows.TransactionAmt end,0))/ CASE WHEN (IFNULL(RevisedEstimatedCost,0)) >= (IFNULL(CASE WHEN SourceCode = 'Blank' THEN Filter Rows.TransactionAmt end,0)) THEN…
-
ELSE Part of Beast Mode Not Calcualting Correctly
ELSE portion of Beast Mode Not Calculating Correctly It should be showing 1,015.56 but instead it is showing Zero. You can see the TA Column has broken out the Else Portion of the CASE statement and populates just fine. CASE WHEN SUM(IFNULL(RevisedEstimatedCost,0)) >= SUM(IFNULL(CASE WHEN SourceCode = 'AP' THEN…
-
Beast Mode Formula is not working as expected
BEAST MODE - % SPENT (IFNULL(case when `SourceCode` = 'AP' then `TransactionAmt` end,0)) / (IFNULL(`ProjTotalCostEOJ 1`,0)) fyi - Transaction Amount = JTD and ProjTotalCostEOJ1 is a column brought in VIA ETL
-
BEAST MODE NOT WORKING AS EXPECTED
BEAST MODE – AMOUNT LEFT CASE WHEN (IFNULL(`RevisedEstimatedCost`,0)) >= (IFNULL(case when `SourceCode` = 'AP' then `TransactionAmt` end,0)) THEN (IFNULL(`RevisedEstimatedCost`,0)) - (IFNULL(case when `SourceCode` = 'AP' then `TransactionAmt` end,0)) ELSE (IFNULL(case when `SourceCode` = 'AP' then `TransactionAmt` end,0))…
-
Beast Mode Not Working when a Value is ZERO
I have a Beast Mode that is not Calculating when one of the Values is ZERO. In this example Projected Cost Should be 21,192 based on the Beast Mode Equation but instead is returning a ZERO Value. BEAST MODE BELOW /Projected Actual Cost is equal to the Greater of Revised Estimated Cost or Job To Date Cost/ CASE WHEN…
-
Card Column Total Not summing correctly
I have a Column Phase InVoice Total that is not summing correctly compared to the other Columns and I can not figure out why. Anyone have any thoughts? Here is the Card and Portion of the ETL that creates that Column.
-
Beast Mode Calculation Help Needed
I need to create a new Column via Beast mode called % Sales Material based on the following calculation. (Sales Price - Actual Cost) / Sales Price I am struggling with the exact Beast Mode Code because Sales Price is on one Line Based on WTSTEP 000 And Actual Cost is on a Separate Line and is based on Beast Mode Where when…
-
HELP! Can not get this ETL Formula to Validate
Can not get this ETL Formula to Validate CASE WHEN HdrParentItemCode IN ('ZROUGHIN', 'ZWATER', 'ZSEWER', 'ZROCK', 'ZVANGUARD') THEN 'Rough-IN' ELSE CASE WHEN HdrParentItemCode IN ('ZTOPOUT', 'ZGAS') THEN 'Topout' ELSE CASE WHEN HdrParentItemCode IN ('ZTRIM','ZDROPIN') THEN 'Trim' ELSE CASE WHEN HdrParentItemCode IN…
