I have a 2 separate table one based on create date and another one based on resolved date values of status “Resolved”. I need to find the difference of 2 table value as attached in screenshot.
Pls help me to find the difference of 2 tables
Pls help me to find the difference of 2 tables
You likely need to create a calculated field something like:
countOver(status, [Assignedfolderlabel, Status, Resolveddate], PRE_AGG) - countOver(status, [Assignedfolderlabel, Status, Createdate], PRE_AGG)
Hi @aswilson ,
Could u pls tell how to use this in the table?
I try to use it but the count is wrong.
For example:
Folder “Brand Frist Migration” difference of 2 table is 50. You can refer the above screen shot of 2 table but here which is showing wrong count.
I don’t know how your data is structured. If you have duplicates, you may need to use distinctCountOver like:
distinctCountOver(status, [Assignedfolderlabel, Status, Resolveddate], PRE_AGG) - distinctCountOver(status, [Assignedfolderlabel, Status, Createdate], PRE_AGG)
If you want to show the calculations post aggregation and filters, then you may need to specify POST_AGG_FILTER. In that case, you need to aggregate your values which you can often do by just wrapping in a min(). That would give you something like this:
distinctCountOver(min(status), [Assignedfolderlabel, Status, Resolveddate], POST_AGG_FILTER) - distinctCountOver(min(status), [Assignedfolderlabel, Status, Createdate], POST_AGG_FILTER)
Since your dataset is unique, the best thing to do is create multiple calculated fields with the components and then add them to the table so that you can see what the results are. For example, I might have the first table column show the system count by resolveddate as you did in your original example, then have a calculated field like:
distinctCountOver(status, [Assignedfolderlabel, Status, Resolveddate], PRE_AGG)
so you can see if your calculation is correctly counting the status by resolved date. Tweak your calculations until you see the right component values, then merge them into a new calculated field that subtracts one from the other.
@aswilson above formula giving me overall count but i want to differentiate by week wise count. So that i can able to provide the weekly carry forward resolved SIMs count.
Or else do you have any logic to calculate carry forward resolved SIM which means SIMs raised in pervious week but resolved in current week.
You could add another grouping term to your countOvers. One approach might be to number weeks 1-52 and then group over them. Or change createdDate and resolvedDate to createdWeek and resolvedWeek and group over those.