I have a recipe with 2 objects - Resolutions and Transactions
Each resolution can have multiple transactions with the same resolution name,
From Resolutions, I have
Dimension - Resolution name
Measure – Total Fee
From Transactions, I have
Dimension – Transaction name
Measure – Amount
Date – Date paid
Every resolution can have multiple transactions with the same resolution name
I have joined transactions with resolutions Inner join
In my lens I have filtered my data with Date Paid (from Transactions)
Selected Columns
Amount – From transaction
Total fee from resolutions
Rows
Settler (from transactions)
Since each resolution can have multiple transactions with the same resolution name,
The problem I am, facing is that the Total fee (from resolutions) is getting duplicated with in the lens inflating this amount incorrectly. I need a solution to dedupe the total fee i.e. I want the sum of Total Fee only for the unique resolutions
What can I do to fix this?
Can I fix it through the SAQL query? Or
Is there a formula I can add in the lens to dedupe this?
This is my current query
q = load "Copy2TestHSResolutionsandTransactions";
q = filter q by date('Join2.litify_fin__lit_Date_Paid__c_Year', 'Join2.litify_fin__lit_Date_Paid__c_Month', 'Join2.litify_fin__lit_Date_Paid__c_Day') in [dateRange([2024,7,1], [2024,7,30])];
q = group q by 'Join2.Settler__c';
q = foreach q generate q.'Join2.Settler__c' as 'Join2.Settler__c', unique(q.'Join2.litify_pm__Resolution__c.Name') as 'A', sum(q.'Join2.litify_fin__lit_Amount__c') as 'B', sum(q.'Join2.litify_pm__Resolution__c.litify_pm__Gross_Attorney_Fee__c') as 'C';
q = order q by 'Join2.Settler__c' asc;
q = limit q 2000;
Now I'm lost again ...
I thought you want to have all detail rows and then total in one query to show it in a table. With regards to showing it in a chart, please see my post above where I mentioned that I would split it into a number showing the total and a chart showing the details by settler.
If you don't want to have the totals included within the chart, but in a separate query, you can split your existing query: one is ending before you start to calculate the total (the s-stream) and the other one ends before you combine the two streams.
Query 1:
q = load "Copy2TestHSResolutionsandTransactions";
q = filter q by date('Join2.litify_fin__lit_Date_Paid__c_Year', 'Join2.litify_fin__lit_Date_Paid__c_Month', 'Join2.litify_fin__lit_Date_Paid__c_Day') in [dateRange([2024,7,1], [2024,7,30])];
q = group q by ('Join2.Settler__c');
q = foreach q generate q.'Join2.Settler__c' as 'Settler', unique ('Name') as 'Resolutions', sum(q.'Join2.litify_fin__lit_Amount__c') as 'Amount', avg(q.'Join2.litify_pm__Resolution__c.litify_pm__Gross_Attorney_Fee__c') as 'Total Fee';
Query 2:
q = load "Copy2TestHSResolutionsandTransactions";
q = filter q by date('Join2.litify_fin__lit_Date_Paid__c_Year', 'Join2.litify_fin__lit_Date_Paid__c_Month', 'Join2.litify_fin__lit_Date_Paid__c_Day') in [dateRange([2024,7,1], [2024,7,30])];
q = group q by ('Join2.Settler__c');
q = foreach q generate q.'Join2.Settler__c' as 'Settler', unique ('Name') as 'Resolutions', sum(q.'Join2.litify_fin__lit_Amount__c') as 'Amount', avg(q.'Join2.litify_pm__Resolution__c.litify_pm__Gross_Attorney_Fee__c') as 'Total Fee';
s = group q by all;
s = foreach s generate sum('Resolutions') as 'Resolutions', sum('Amount') as 'Amount',sum('Total Fee') as 'Total Fee';
Use Query 1 to display it in a bar chart, Query 2 can be displayed in number widgets.
