topic Re: Variance between Min(Date) and Max(Date) in QlikView

Variance between Min(Date) and Max(Date)

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

Hi Experts,

Please refer to attached working document QVW. I am trying to create three different Straight Tables.

I am stumped as to how to adjust the straight tables such that the straight tables would do a direct comparison of the two months (min date and max date). So if January through March are selected in the Date slider, it should do a comparison between January (min date) and March (max date). If February through March are selected, it should do a comparison between February (min date) and March (max date).

One straight table should pull as Dimension the ID's that are in Max(Date) but not in Min(Date). In the attached example, if January through March are selected, it should be:

ID	Rating	Type
XYZ999	5	Operational
XYZ123	4	Operational
ZZZ999	5	Operational

Another table should pull as Dimension the ID's that are in Min(Date) but not in Max(Date). In the attached example, if January through March are selected, it should be:

ID	Rating	Type
DEF456	5	Non-Operational

Another table should show ID's that are existing in both Min(Date) and Max(Date) but Size is different. In the attached example, if January through March are selected, it should be:

ID	Former Size	New Size
ABC123	1000	2000

Thanks a bunch in advance! Been going at this for days.

Re: Variance between Min(Date) and Max(Date)

lironbaram — Tue, 15 Nov 2016 05:53:54 GMT

have a look at the attach example

you can achieve yoiur request by using set analysis in the calculated dimension

Re: Variance between Min(Date) and Max(Date)

Anonymous — Tue, 15 Nov 2016 08:09:09 GMT

Wow! This is exactly what I am looking for. Thanks so much!!!

I'm having trouble transforming your 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.

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

Would you be able to advise?

Re: Variance between Min(Date) and Max(Date)

lironbaram — Tue, 15 Nov 2016 20:01:19 GMT

have a look at this

Re: Variance between Min(Date) and Max(Date)

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

Thanks! I'm stumped with one last thing - would you be able to help? I'm trying to create a text object showing the Sum of the total variance in Size of records that exist in both min(date) and max(date) but have changed in Size.

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 my calculaton. Thanks again for the help!