Still not working. With that, for pentesting hours we see this if its for the past 6 weeks -
But then this in the last 7 weeks -
we previously confirmed the 3,397 number is correct. It looks like when we change the dates filter its puling May hours into June which doesn’t make sense.
So Actually, that worked. I updated the dateMonth to entry date and that did it. However, now I’m getting an error when I try and add the Sumover (Pentesting Hours + Support Hours)/billable hours and it says that I can’t use an aggregate and non aggregate. is there anything I can do to ignore it. It has all the info I need in the chart I just need a basic calculation.
Hi @doneliza,
Glad to hear! Could you just share the calc. fields for pentesting hours, support hours and billable hours, we probably need to convert them all to sumOver’s.
Makes sense! Pentesting and support are sumover. Billable hours is (distinct_countIf(owner, {hour type: name}<>“Training”)*168)-(sumIf(hrs, {hour type: name}=“PTO”))
Can we turn that into a sumover?
Hi @doneliza,
It could look something like this:
(distinctCountOver(ifelse({hour type:name} <> "Training", {owner}, NULL), [{Date Month}], PRE_AGG)*168)-sumOver(ifelse({hour type:name}="PTO", {hrs}, NULL), [{Date Month}], PRE_AGG)
@doneliza, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!
Ah sorry I missed that, unfortunately, this did not work. It still throws an error. Any other ideas?
Any other ideas? the distinctcountover worked as a calculated field but when I tried to add it to the graph it threw an error. Please let me know when you have a chance. Thanks!
Its saying mismatched aggregation for billable hours;
(distinctCountOver(ifelse({hour type:name} <> "Training", {owner}, NULL), [{Date Month}], PRE_AGG)*168)-sumOver(ifelse({hour type:name}="PTO", {hrs}, NULL), [{Date Month}], PRE_AGG)
[/quote]
Hi @doneliza,
Could you break this up in to 2 calculated fields so then we can see where the error is coming from:
(distinctCountOver(ifelse({hour type:name} <> “Training”, {owner}, NULL), [{Date Month}], PRE_AGG)*168)
sumOver(ifelse({hour type:name}=“PTO”, {hrs}, NULL), [{Date Month}], PRE_AGG)
Let me know what the error you receive is and what’s setting it off.
Looks like its this one - (distinctCountOver(ifelse({hour type: name} <> “Training”, {owner}, NULL), [{dateMonth}], PRE_AGG)*168)), the other one works.
Is there a sumover I could use for the above?
Hi @doneliza,
Does it say what the error is when scrolling your mouse over the error line? I tested the following calculated field in an analysis and did not receive an error.
(distinctCountOver(ifelse(Product <> 'Alchemy', {Customer ID}, NULL), [{Date Month}], PRE_AGG)*168)-sumOver(ifelse(Product = 'Storage', Quantity, NULL), [{Date Month}], PRE_AGG)
The fields are not exactly the same since I have different data to work from but from not receiving an error on my end, I’m thinking it may have something to do with a typo or issue with [hour type:name] field.
For a sumOver, maybe you could try something like this:
sumOver(ifelse({hour type: name} <> "Training", 1, 0), [{dateMonth}], PRE_AGG) * 168
Its telling me distinctcount - sumover is mismatched aggregations, which is why I can’t do the first. For the second, we’re trying to find the number of testers*168 - PTO hours so Im not sure that would work. Its frustrating because I have everything I need on the chart
Pentesting hours = sumOver(ifelse({hour type: name}=“Task Time” AND project<>“Pentest Support Schedule” AND {hour type: name}<>“Non Pentest Time”, hrs, NULL), [dateMonth], PRE_AGG)
Support Hours = sumOver(ifelse({hour type: name}=“Issue Time” OR {hour type: name}=“Task Time” AND project=“Pentest Support Schedule” OR project=“Pentest Support Adhoc Tasks”, hrs, NULL), [dateMonth], PRE_AGG)
Possible Hours = distinct_countIf(owner, {hour type: name}<>“Training”)*168
PTO Hours = sumIf(hrs, {hour type: name}=“PTO”)
All of these are showing the correct details and I just need to do,
(Pentesting Hours +Support Hours)/ (Possible Hours - PTO)
But it just says mismatched aggregation every time. And when I’ve tried to switch out some of the recommended sumovers above, the numbers don’t match and I know the above numbers are correct. Any suggestions?
Hi @doneliza,
distinctcountover and sumover will not cause misaggregations, they do have the ability to work together as they’re both LAC-W functions.
Looking at everything listed here, I believe the problem is more likely stemming from the ‘possible hours’ and ‘PTO hours’ as you’re using distinct_countif and sumif. Those would need to be converted to distinctcountover and sumover with an ifelse statement nested inside. Hopefully that will do the trick!
Okay so trying (distinctCountOver({owner}, {dateMonth}, PRE_AGG)*168)-sumOver(ifelse({hour type: name}=‘PTO’, {hrs}, NULL), [{dateMonth}], PRE_AGG)
But it’s saying my distintcountover is wrong. Do you see where?
Okay, so all has been updated and everything is aggregated but now… its showing an insanely high amount when I divide. Including the graph here for viz. Pentesting Hours + Support Hours/ Billable hours should be roughly 70% but its showing 288,714%. I assume this is a min issue but I’m not sure what path to take forward. Any advice?
solved it by doing Min(Pentesting Hours + Support Hours)/Min(Billable Hours). Closing out.
2 Likes