Quicksight Calculated Fields

Q&A
, , ,
1

0

I am creating vizs in QS and need help with calculations. It is easier for me using PowerBI and DAX which I am more familiar with and can use formulas like ALL, FILTER, Sameperiodlastyear etc to get the results I need, but unfortunately are not available in QS or a way to translate them to equivalent QS formulas.

For example I need to make the sum of a numerical column static rather than the default dynamic aggregation and use as denominator in value calculations by groups Tried the sumover function but the Prefilter, preagg and postaggfilter calculation levels are still confusing.

I have attached sample data

sd1
sd11428×838 146 KB

These are some of the calculations I need to do and repeated for the other dimensions:
I have tried different solutions but the results appear wrong on checking

Total Amount by year,
also by quarter
Total Quantity by year,
also by quarter

Companyshareamount by year= Amount by Companyandyear/ Total amount for that Year

Company amount PerYear
companyshareperyear percentage
companytransamountperQt_Yr
transamountperQt_Yr
companyshareperQt_Yr
?Year = extract(‘YYYY’, date) coming as a number
?Quarter= extract(‘Q’, date) tried to use in the sumover function but getting an error

?companyshare= sumOver(sum(amount),[company], POST_AGG_FILTER)/sumOver(sum(amount), )
the documentation says no aggregation with pref or preagg

?companyshareamountperQt for selected year = CompanyamountperQt/totalamountperQt

Company share for selected year- share for previous year
I have tried creating year filters and parameters to pass into the formulas to group or partition by year

Kindly help. Thanks in Advance

1 Like
2

Hello @Tes, welcome to the Quick Sight community!

It definitely sounds like you are going to want to utilize LAC-W calculations in order to accomplish these various calculated fields. I’ll try providing some insight on a few of the options you mentioned above. In regards to Total of a value by Year and Quarter, you can try something like this:
Total Amount by year = sumOver({Amount}, [truncDate('YYYY', {Date}], PRE_AGG)
Total Amount by quarter = sumOver({Amount}, [truncDate('Q', {Date}], PRE_AGG)

Things you will notice here. I am using the truncDate function to ensure I am getting the target period for my partition field. This way any matching values will be summed in the sumOver function. Also, I will regularly default to the PRE_AGG calculation level since PRE_FILTER targets filters applied at the dataset level and I rarely utilize those.

Then, in order to create something like Companyshareamount by year, you will want to use a similar function. One thing to remember, if you utilize LAC-W aggregations, both the numerator and the denominator will require that same level of aggregation. Here is an example:
Companyshareamount by year = sumOver({Amount}, [{Company}, truncDate('YYYY', {Date})], PRE_AGG)/ sumOver({Amount}, [truncDate('YYYY', {Date})], PRE_AGG)

In the numerator function, you will be calculating the total amount for each company in each year. That is what the partition field [{Company}, truncDate('YYYY', {Date})] is handling. Then the denominator will just get the total amount for that year. This should return the percent value you are looking for.

I know this doesn’t answer each calculated field you mentioned, but I hope this gives you some of the insight you are looking for when using LAC-W aggregations in Quick Sight. Let me know if you have any further questions!

1 Like
3

Thank you very much for these suggestions @DylanM, they have been very helpful.

I just need this calculation now: 'Company share for selected year1 - company share for selected year2
based on 2 selected years using either filter or parameter. thank you

4

Hello @Tes, for something like that, you will probably want to calculated fields to return dates that match the parameters, then we can use those on LAC-W functions. If your selected year is returning just the value like this, 2024, then we will need to use extract. If it is returning a date field like this, 01/01/2024, then we will need to use truncDate. I will use both examples below to demonstrate. It would look something like this:

selectedYear1 = ifelse(${year1} = extract('YYYY', {date}), {date}, NULL)
selectedYear2 = ifelse(${year2} = truncDate('YYYY', {date}), {date}, NULL)

Now, we can use a LAC-W function to subtract the total values from each other:
sumOver({Amount}, [truncDate('YYYY', {selectedYear1})], PRE_AGG)/sumOver({Amount}, [truncDate('YYYY', {selectedYear2})], PRE_AGG)

An alternative would be to use the calculate fields above that return date to return amount instead, then your final calculated field would look like this instead:
sumOver({selectedYear1Amount}, [], PRE_AGG)/sumOver({selectedYear2Amount}, [], PRE_AGG)

Those are 2 ways to achieve your expected output. I’ll mark this as the solution, but please let me know if you have any further questions. Thank you!

1 Like