Hello
I have a Pivot table , where My Active head count and Expected Headcount sre dependent on each other. But I am facing some issues with the calculation
Hi @DylanM ,could you please help on this? Since the calculation is cyclical, while trying to solve active , Expected is breaking, while trying to solve expected , active is breaking
Hello @Ganga, since we are continuing from a previous topic, I will close out the last question and we can continue from here. Initially, I will say, creating a demo version of this analysis in QuickSight Arena will make it a lot easier for us to debug the issues you are facing.
As for the aggregation error you are facing, are your hires and terms fields also calculated fields? If so, can you please share those with me as well?
Hi @DylanM , These are the calculations that i am using
1.Active headcount= ifelse(isNull({Lag for prior}),sum({active_head_count}),{Lag for prior})
2.Lag for prior= lag(sum({Expected Ending Headcount}),[date ASC],1)
3.Hires=(hires)${AddHires}
4.Terms= terms+${AddTerms}
5. Expected Ending head count= {Active headcount}+Hires+Terms
Hello @Ganga, after spending some time with the calculations, I was able to set it up in a way that I believe resolves your issue. You can view my work here:
Hi @DylanM ,
Thankyou so much for the workaround. But this is not working as expected. As I was mentioned in the Issue snapshot,
For the following month of selected date, we need the Actual Active Head Count which is pulling from database (active_head_count)in Active headcount for April here. ie, 45
And expected Ending headcount should be the sum of first 3 rows.
Ie, 45+3+10= 58
For next month, Active headcount should be previous month’s Expected Ending Headcount. as of now you are taking (active_head_count) instead of that.
Ie, 58
And for the Expected ending headcount for May Month should be Sum of Top 3 Rows.
Ie, 58+9+7=74
And this 74 Have to be the Active Headcount for Jun month.
Here , Either we need to get a Way to do the addition of a calculated field containing lag function with other columns, or need to get if we can use any other function other than lag, so that it wont cause any circular dependency issues.
Hello @Ganga, I appreciate the explanation, I think I fully understand the output you are expecting.
The main issue you are going to run into here is the number of aggregation types that you are trying to utilize together. I was able to create a calculation to run the Expected Ending Headcount but I cannot also seem to return the active head count value you are expecting. Using runningSum with firstValue functions work together to get the desired result, but then I cannot nest those aggregations in a Lag function to return the previous value.
Based on my knowledge in QuickSight, you will likely not achieve the exact solution you are working towards without hitting limitations.
My best recommendation would be to utilize the Expecting Ending Headcount calculated field that I built in [Arena](Multiple aggregation calculations), while finding a different way to represent the Active Head Count row. I would not recommend trying to reference calculated fields within themselves because that will definitely lead to complicated errors. I hope this helps!
