Variance between Min(Date) and Max(Date)

[Anonymous]

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.

[lironbaram · Accepted solution]

hi

have a look at the attach example

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

[Anonymous]

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?

[lironbaram]

have a look at this

[Anonymous]

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!