FIXED () in Magic ETL?

verytiredgirl
verytiredgirl Member
edited January 10 in Magic ETL 

'MTD'
/


SUM(

CASE

WHEN Class 3 = 'Sales'

THEN Amount

ELSE 0

END

)

FIXED ()

I have this BeastMode called 'MTD %' that calculate the % of each GL Account based on Sales (so Sales = 100%)And it was working correctly with different branches. Just wondering if I can create this in ETL? 
                

                
            
            

        

    




    
Answers
            
    
                        
  • 
            
    

                                                                
    
                    ColemenWilson                    
                        
    
                        
                                ColemenWilson
                        

                        
                             Coach                         
                    
    
                    
    
                        
                                                   
                         edited January 10                                             
    
                    
                                                
    
                                                    
    
                                    
    
                
    
                    
    
                        
    
                            
    Fixed functions only work in beast modes. To do this in ETL you will need to add a constant if one doesn't already exist, this will be needed for a Group By to sum up Amount. This step replicates the first part of the fixed function by getting you a total amount for sales for your grouping. The next step is to join the grouped data back to the rest of your data. Then you can compare the MTD to the total summed amount. 
                            
    
                        
    If I solved your problem, please select "yes" above
                
                        
    
                
    
            
    
        
    • 
                
  • 
            
    

                                                                
    
                    rco                    
                        
    
                        
                                rco
                        

                        
                             Domo Employee                        
                    
    
                    
    
                        
                                                   
                                                                    
    
                    
                                                
    
                                                    
    
                                    
    
                
    
                    
    
                        
    
                            
    There is no "FIXED()" in Magic ETL, but you can replicate its behavior with a combination of Group By and Join. In this case, it you'll want to configure a Group By like this:

    
    
    
        
            image.png
    
    

    

    And the subsequent Join like this:
    
    
    
        
            image.png
    
    

    

    And finally an Add Formula like this:
    
    
    
        
            image.png
    
    

    

    Arranged like this:
    
    
    
        
            image.png
    
    

    

                            
    
                        
    Randall Oveson <randall.oveson@domo.com>
                
                        
    
                
    
            
    
        
    • 
                
  • 
            
    

                                                                
    
                    verytiredgirl                    
                        
    
                        
                                verytiredgirl
                        

                        
                             Member                        
                    
    
                    
    
                        
                                                   
                         edited January 10                                             
    
                    
                                                
    
                                                    
    
                                    
    
                
    
                    
    
                        
    
                            
    @rco thanks for this, but might I ask why it's a cross join? 
    
because my end goal is to have a Pivot Table that has column: MTD, MTD %. With the Cross Join, the MTD Amount will be inflated in the Pivot Table
                            
    
                        
                
                        
    
                
    
            
    
        
    • 
                
  • 
            
    

                                                                
    
                    rco                    
                        
    
                        
                                rco
                        

                        
                             Domo Employee                        
                    
    
                    
    
                        
                                                   
                                                                    
    
                    
                                                
    
                                                    
    
                                    
    
                
    
                    
    
                        
    
                            
    The Group By emits just one row, the sum for the whole table. The cross join just joins that single sum value to the original table; the final row count matches the original row count.
                            
    
                        
    Randall Oveson <randall.oveson@domo.com>
                
                        
    
                
    
            
    
        
    • 
                    

            
    

                                

                                

                                
                                

                                        
    
    


        
Categories