I need to compare volumes of values in a few different fields. It is to track how many records change states and whether the volume of records for that state is going up or down compared to yesterday or the week before.
Example of Data:
|ID|Current State|State Yesterday|State 1 Week Ago|
|1|A|B|B|
|2|A|A|A|
|3|A|B|C|
|4|B|C|D|
|5|B|B|B|
|6|A|B|D|
|7|A|C|D|
|8|C|C|C|
|9|C|D|D|
|10|C|D|E|
So my table would look like: (‘Difference’ being the volume change from volume at state vs volume at State Yesterday etc…)
State
Volume
Difference from Yesterday
Difference from 1 Week
A
5
4
4
B
2
-2
0
C
3
0
1
D
0
-2
-4
E
0
0
-1
I hope this makes sense. If you need me to provide more information then please just let me know, many thanks.
@riley.anderson -
Did Max’s solution solve your issue? If so, would you please mark as Solution. If not, please let us know if you are still having an issue.
I have managed to add a number version of the state fields and also added the calculated field you suggested but it doesn’t work, or I’m applying it incorrectly.
I don’t understand the logic behind it because of how I need to group by state which is the current state field. So not sure how the value fields could possibly relate to the group by state column when the counts/sum for the state yesterday should be independent of the state field.
Please let me know if you need more information. Any questions and please just ask, many thanks.
I’m not getting an error. Just using the given formula doesn’t work and I don’t understand how it could in the way I’m applying it.
To create an integer version of the states and state yesterday etc… I have used this formula:
parseInt(concat(toString(ifelse(casetype = ‘ABC’, ‘1’, casetype = ‘BCD’, ‘2’, casetype = ‘CDE’, ‘3’, casetype = ‘DEF’, ‘4’, casetype = ‘EFG’, ‘5’, ‘6’)), replace(toString({group_state_display_order}),‘.0000’,‘’), replace(toString({display_order}),‘.0000’,‘’)))
Obviously I want this as a single table which is grouped by state so it can show the volume of records currently at that state, and the adjacent columns could either be a count of how many were at that state yesterday or show the difference in how many are it it now compared to yesterday.
I have a calculated field called ‘Difference from 7 Days Ago’ = sum({State Number}) - sum({State Number 7 Days Ago}). I have applied it here:
I’m not sure how to do it without grouping by state but by grouping by state then it’s not given an independent look at states from 7 days ago as it is grouping by the state they were at for that row if that’s correct?
Also, not sure why that column is showing so many decimal places when I have it set to show 0.
Any help would be greatly appreciated, many thanks.
@Max Would you want me to put it as a value? If so, what aggregate would you want to see?
The formula I gave is just an easy way to for me to get integer versions of my states. A state has a state group and both of the state and state group have an order number (as in to be ordered by) so by concatenating firstly a ‘casetype’ int and then the two of group state int and state int then it creates a unique integer for each state per casetype which is what I was trying to achieve.
Let’s take a step back.
I looked into your original query. What you are trying to do can be accomplished if you can introduce dummy records that represent each unique state value across all time variants.
Will explain using the same data sample you initially provided.
Your unique states therein are A through E.
So, you got to add five additional records to the dataset as shown below
|-1|A|A|A|
|-1|B|B|B|
…
|-1|E|E|E|
Now, you can use the following calculations Current State# countOver(1,[{Current State}],PRE_FILTER) - 1
1 week ago # countOver(1,[{State 1 Week Ago}],PRE_FILTER) - 1
Diff from yesterday {Current State#} - {Yesterday #}
Diff from 1 week {Current State#} - {1 week ago #}
Now, pull the Current state, Current State#, Diff from yesterday and Diff from 1 week into a table and filter that table to show only the dummy id records (-1).
This will get you the exact result you were looking for. See screen shot below.
