but our expected results not matching
expected result should be:
7/31/2024 $13,568.80
calculation logic for expected result is calculating each agebucket max amount : 3734.84+(-3202.84)+3173.06+7177.41+7376.02+(-4689.69)=13,568.80
Hi @rahul8
Welcome to the quicksight community!
Your current formula calculates daily-level( max values per AgeBucket), then sums them by day. You need monthly aggregation (summing unique AgeBucket values per month).
Try the following formula:
sumOver(
maxOver(
{InsuranceCredit},
[truncDate(‘MM’, {PeriodEndDate}), {AgeBucket}], // Group by MONTH
PRE_AGG
),
[truncDate(‘MM’, {PeriodEndDate})], // Sum across the month
PRE_AGG
)
If this helped you, please mark my answer as solution. That helps the community to find solutions faster.
Hello,
thank you, this formula works perfectly with the data, we have it successfully rule out the duplicate values which are getting calculated automatically.
But there is one more problem we are facing.
So, it was working fine when we have single repetitive values in the column.
But now when we introduce new column which is practice ID, the count over numbers got mismatched. Therefore, average number got mismatched as there are two or three duplicate values in single row.
for ex -
in the given below screenshot - the Max IC, Count IC, Org IC is calculated using this Calculated field shown below respectively.
Max IC is:
maxOver(
InsuranceCredit,
[PracticeID,{month date},AgeBucket],
PRE_AGG
)
Count IC is :
countOver(
InsuranceCredit,
[{month date},AgeBucket,PracticeID],
PRE_AGG
)
let say in above image which highlighted values contains in table are showing May month data with having agebucket as 121-180 having two Amounts because of two practice id - 6 i.e -1632/17 = -96 and practice id 664 - 0/3=0.
so as per the data calculation is correct.
But When we want to show the Values in graph, so as per requirement we created the graph as shown below.
its giving taking sum of both Max ic which is = -1632+0 and divide it by 20 ( 17 + 3 ) = 81.6 which makes average calculation wrong.
we want -96 as the final output.
Hello @jaikji96,
You can try:
Calculate Sum and Count per PracticeID
Max IC per PracticeID:
maxOver(
{InsuranceCredit},
[PracticeID, {PeriodEndDate}, AgeBucket],
PRE_AGG
)
Count IC:
countOver(
{InsuranceCredit},
[PracticeID, {PeriodEndDate}, AgeBucket],
PRE_AGG
)
Average IC:
maxOver({InsuranceCredit}, [PracticeID, {PeriodEndDate}, AgeBucket], PRE_AGG)
/
countOver({InsuranceCredit}, [PracticeID, {PeriodEndDate}, AgeBucket], PRE_AGG)
sum the total InsuranceCredit and total count across all PracticeIDs for the group:
sumOver(
maxOver({InsuranceCredit}, [PracticeID, {PeriodEndDate}, AgeBucket], PRE_AGG),
[{PeriodEndDate}, AgeBucket],
PRE_AGG
)
/
sumOver(
countOver({InsuranceCredit}, [PracticeID, {PeriodEndDate}, AgeBucket], PRE_AGG),
[{PeriodEndDate}, AgeBucket],
PRE_AGG
)
Hi @rahul8,
It’s been awhile since we last heard from you on this thread, did you have any additional questions or did one of the responses provided above help with your case?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Hi @rahul8,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.
