I have a dataset with several fields: stat_date for storing dates and times, department_id for storing department information, bal_120 for cumulative amounts updating at different times, and total_balance for cumulative balance updating at different times as well.
I’ve created two calculated fields, Daily_new and Daily_total_bal, which represent the maximum values of bal_120 and total_balance for each day and department.
Now, I want to calculate a new field that gives the percentage of Daily_new divided by Daily_total_bal, but only considering the maximum values for each department. For example, on January 28 at 7 pm, the bal_120 for the NS department is 193889.55, and the total_balance is highest at 6 am on the same day, which is 2044429.32. I want to calculate this percentage for all departments.
Hey @Abhishek_24 ,
could you provide the calculation for daily_new and dauky_total_bal? Just to test how it can work.
My first thought would be to use:
sum(Daily_new,[department_id,stat_date without time])/sum(Daily_total_bal,[department_id,stat_date without time])*100
But I’m not sure if this one would work.
@robert.eibers
Also, is there a way if i want to show the same, by last entry stored .
For example , in above Image Jan 28, 2024, 7 pm will be the latest entry.
while in some cases it can be 6 am.
and percentage can be calculated from the corresponding rows.
as shown in this image, for jan 29, 2024 6am is the latest entry for this day, while for jan 28 its at 7pm
Hey @Abhishek_24 ,
for your first question from yesterday something like:
maxOver(max({bal_120}),[truncDate(‘DD’, {stat_date}), {department_id}])
/
maxOver(max({total_balance}),[truncDate(‘DD’, {stat_date}), {department_id}])*100
For your follow-up question, I need some time. But i hope the first part can help you.
Um, it is doing, but i want Latest entries should only show.
for example if Feb 1 has entries for 3 timestamp, i.e 12 AM, 6 AM and & 7PM.
then by latest entry feb 1 7PM are the one’s.
same for all days.
Hello @Abhishek_24, to manage your last question, I would recommend using the lastValue function in Quick Sight to retrieve the last value partitioned by day and department. That should give you the result you are looking for!
If you need any further assistance with this issue, please post a new question in the community. That will ensure you are at the top of the priority list for a response from one of our Quick Sight experts. Thank you!
