topic Re: Individual/Detailed Variances in QlikView

Individual/Detailed Variances

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!!!

Re: Individual/Detailed Variances

sunny_talwar — Tue, 08 Nov 2016 13:08:35 GMT

Is this what you are aiming for?

Expressions

1) Added New

=If(Date <> Min(TOTAL Date), Concat(Aggr(If(ID <> Above(ID), ID), ID, Date), ', '))

2) Removed

=If(Date <> Max(TOTAL Date), Concat(Aggr(If(ID <> Below(ID), ID), ID, Date), ', '))

Re: Individual/Detailed Variances

Anonymous — Tue, 08 Nov 2016 14:02:27 GMT

Sunny, thank you and this is very helpful. This solves listing which ones were dropped and added. However I was also trying to create a straight table where the dimensions are the individual ID's that were added and then pull in their Rating and Type similar to below:

NEW RECORDS FOR FEBRUARY:

ID	Rating	Type
XYZ999	5	Operational
XYZ123	4	Operational

And then for those records that were dropped the table would be:

RECORDS DELETED AFTER JANUARY:

ID	Rating	Type
DEF456	5	Non-Operational

And then for records which are present in both dates but have a change in their Size, the table would be:

RECORDS WITH CHANGES IN SIZE

ID	Former Size	New Size
ABC123	1000	2000

Is this doable? Thanks for the help.

Re: Individual/Detailed Variances

sunny_talwar — Tue, 08 Nov 2016 14:21:14 GMT

If this is always a comparison between 2 months, you can try like the attached

Re: Individual/Detailed Variances

Anonymous — Tue, 08 Nov 2016 14:53:59 GMT

Yes it would always be comparison of two months.

Amazing! This looks great! Thanks! Will play around with it and work with the script. But so far, this looks like exactly what I am looking for.

Re: Individual/Detailed Variances

sunny_talwar — Tue, 15 Nov 2016 10:18:44 GMT

Did you get it to work for 3 month selection scenario? I was still working on it? Not needed anymore?

Re: Individual/Detailed Variances

Anonymous — Tue, 15 Nov 2016 10:27:26 GMT

Thanks Sunny for reaching out. I was able to get help from another person and was able to resolve this. The straight tables have been updated (attached here for reference).

However, if you are able to help, I am trying to accomplish putting the total Size in a text box. I'm having trouble transforming the script from the Dimension of the Straight Table and using it for a text box showing the Sum(Size) of new records (i.e. records in max(date) but not in min(date)). It is not calculating anything and I think I am applying the AGGR function incorrectly.

So on the attached, if January through March are selected, the textbox should read 1850 (i.e. the total Size of XYZ999, XYZ123, and ZZZ999 from March). If January through February are selected, the textbox should read 1050 (i.e. the total Size of XYZ999, XYZ123 from February).

Would you be able to advise?

Re: Individual/Detailed Variances

sunny_talwar — Tue, 15 Nov 2016 10:32:19 GMT

May be this:

=Sum(Aggr(If(Count({<Date={"$(=max(Date))"}>}DISTINCT ID) = 1 and Count({<Date={"$(=min(Date))"}>}DISTINCT ID) < 1, Sum({<Date = {"$(vMaxDate)"}>} Size)), ID))

Re: Individual/Detailed Variances

Anonymous — Tue, 15 Nov 2016 10:37:56 GMT

Exactly what I was looking for. That's where I got stumped - I never considered applying two SUM functions. Thanks a bunch!!!

Re: Individual/Detailed Variances

sunny_talwar — Tue, 15 Nov 2016 10:39:53 GMT

This should also work using set analysis search string concept:

=Sum({<Date = {"$(vMaxDate)"}, ID = {"=Count({<Date={'$(=max(Date))'}>}DISTINCT ID) = 1 and Count({<Date={'$(=min(Date))'}>}DISTINCT ID) < 1"}>} Size)

Re: Individual/Detailed Variances

Anonymous — Tue, 15 Nov 2016 10:42:47 GMT

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

Re: Individual/Detailed Variances

sunny_talwar — Tue, 15 Nov 2016 10:49:03 GMT

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

Re: Individual/Detailed Variances

Anonymous — Mon, 21 Nov 2016 09:09:00 GMT

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!