Individual/Detailed Variances

[Anonymous]

Hi Experts,

I have 2 datasets. One has generic info for each record. Another table is sort of a historical table showing which records exist for a particular time. This is identified with the Date field. In the example, I have data for January and February.

I've attached the working document here below.

I am able to successfully do a variance on the count of ID's for February versus January. This is good but it simply shows the net difference.

What I want to achieve is be able to something more granular. I would like to list down the actual ID's that are present in January but dropped out of February (in the example it is DEF456) as well as the actual ID's that were only added in February (i.e. in the example these are  XYZ999 and XYZ123). And hopefully put them in separate Straight Tables (one for "Deleted" and one for "Added") where I can also pull the Rating and Type fields associated to those records.

Similar to added/removed ID's, I would also like to put in another Straight Table those ID's which are present in both dates but where the Size is different (i.e. ABC123 has a size of 1000 in January but a size of 2000 in February).

Any advice? I would assume the logic in the script would be similar for the above scenarios but really stumped after several attempts. Thanks for the help!!!

[Anonymous]

Great! Any reason why one method could be better than the other?

[sunny_talwar]

In scenarios where you can use both Aggr() and set analysis, Aggr is considered to be slightly less efficient way to do things. Having said that (unrelated to Aggr() and Set Analysis), recently I have done some testing in some other things where it was mentioned that Method A is better than Method B, but found that Method B is slightly better.

Coming back to this, I would suggest you to spend some time testing to see which one is better (if your data is huge, else you should be good with either of the two)

HTH

Best,

Sunny

[Anonymous]

Thanks! I've been trying both and so far performance is good for either. I have since focused on the set analysis approach in my QVW.

I know this whole discussion is closed, but hoping you can help on one last thing. I added a new column on the 3rd straight table to calculate the variance between former Size and new Size for records that exist in both Min(date) and Max(date) but have changed in Size. Just like the recent one, I am trying to put the total into a text object - this time the Total Variance. Been struggling to get it to work.

Jan through Mar should result in 900

Jan through Feb should result in 1000

Feb through Mar should result in 200

Appreciate any help! Thanks!

UPDATE: Ignore above. Figured it out! Saw what was wrong with what I was doing. Thanks again for the help!