hello community
I have a brief query on how to do to get the sum of a field(amount_usd) taking into account the following calculated field " churn = distinct_countIf({company_id}, parseDate(InvoiceDate) < now())", as my data contains duplicates of company_id, I look forward to your reply!
Hey @Juan ,
it depends sort of what you want to show. You could use a Using level-aware calculations in Amazon QuickSight - Amazon QuickSight to use it as a group by level in that sum field or something like firstValue - Amazon QuickSight if you want to only use the first value by amount usd partition by company ids.
Hopefully that could help you but if you still have questions, feel free to ask.
BR
Robert
Thanks for the reply @robert.eibers ! i want to show the amount_in_usd field, but only for unique companies, as you can see from the image i added, the field amount_in_usd is actually the value of all company_id(even duplicates), but i need the value of the companies from the field ‘Churn’ wich is calculated as follow ’ Churn = distinct_countIf({company_id}, parseDate(FechaFacturacion) < now())’
Hello @Juan, for this scenario would it be unique companies in that month, and if so, do you want a sum of the amount for that month only taking into account 1 amount per company? I’m not certain of your exact expectation here but I will provide an example calculation that should manage your expectations. I would recommend wrapping a maxOver calculation to grab a single amount value per unique company ID for each month, within a sumOver calculation to sum all of the values for the month to get your amount_usd column. It would look something like this:
sumOver(maxOver({amount_in_usd}, [truncDate('MM', {Date}), {company_id}], PRE_AGG), [truncDate('MM', {Date})], PRE_AGG)
You will need to swap out date for the date field you want to utilize. Also, if you don’t want the max for the company but rather the min or average for each company_id per month, that can be changed as well. I will mark this response as the solution, but let me know if you have any follow-up questions on this topic. Thank you!