Set analysis to get expected output

[vvira1316]

Hi,

I'm having challenges to get expected output.

I'm including data file and application for your reference.

Sample Scenario

Id	ProfileId	DIM_FLD1_Id	DIM_FLD2_Id	DIM_FLD3_Id	DIM_FLD4_Id	DATE
1	1027	21	1	0	1	7/7/2016
2	1027	21	1	1	1	4/30/2018
3	1027	21	1	1	7	7/18/2018

DIM_FLD3_Id	BSN
0	BSN1
1	BSN2
2	BSN3
4	BSN4
5	BSN5
6	BSN6

DIM_FLD4_Id	OSN
0	OSN1
1	OSN2
2	OSN3
3	OSN4
4	OSN5
5	OSN6
6	OSN7
7	OSN8

Below is Straight Table Chart in QS

Data Dim	Data Field ID	Application	Employee	Country	Department	Data Max Date	ID
21	1027	ALN21	Employee3	United States	DEPT199	7/18/2018	3
60	1072	ALN60	Employee3	United States	DEPT199	4/30/2018	5
68	1081	ALN68	Employee4	United States	DEPT41	4/30/2018	7
95	1119	ALN95	Employee4	United States	DEPT41	4/30/2018	9

Requirement

1. Straight table contains all Max (Data Date) Records of each DIM_FLD1_Id.

2. Details in table to include BSN, OFFNM, OSN, etc.

    i.e.in above straight table for Data Dim 21 it should show BSN2, OSN8

3. 30 Day ( Today() - 30   )Aging Detail Table (same as above) and Count of records

    for each DIM_FLD1_Ids Max Date i.e. less than or equal to Today() - 30 but  has no record within last 30 days

Thanks & Regards,

Vijay

[sunny_talwar]

May be use this for your second req...

=FirstSortedValue(BSN, -[Data Date])

=FirstSortedValue(OSN, -[Data Date])

Didn't get the 3rd req... what exactly are you looking to do there?

[vvira1316]

Hi Sunny,

Thanks a lot!!!

Your suggestion is helping for 2nd request.

                How do I get count for this straight table?

For 3rd request. I'm going try explaining it again.

The detail table will be same as above. Will need count as well.

Record selection criteria changes to following

a)     Record set to be considered will be all records whose Date is <= Today() - 30, now from this only Max Date for  each DIM_FLD1_Id.

b)     DIM_FLD1_Id record should be removed if it has record with Date > Today() - 30

I hope I'm able to explain it.

BR,

Vijay

[sunny_talwar]

Your suggestion is helping for 2nd request.

                How do I get count for this straight table?

Count for this straight table? Where in a KPI object?

b)     DIM_FLD1_Id record should be removed if it has record with Date > Today() - 30

Not just any date, but the max(Date), right? so Max(Date) >  Today() - 30?

[vvira1316]

Was thinking of count in a KPI object but both place would be great.

It's PoC I'm working on so not sure what business would eventually like to have, so please advise for both.

The other ask it to find Max Date of each DIM_FLD1_Id where Date is less than Today() - 30, but a DIM_FLD1_Id record should not be displayed or counted if it has another record whose Date is within last 30 days.

[sunny_talwar]

Was thinking of count in a KPI object but both place would be great.

I mean the count for the first one should be just Count(DISTINCT DIM_FLD1_Id), isn't it?

The other ask it to find Max Date of each DIM_FLD1_Id where Date is less than Today() - 30, but a DIM_FLD1_Id record should not be displayed or counted if it has another record whose Date is within last 30 days.

Oh so, if there is a record within Today() - 30 and Today(), that DIM_FLD1_Id should not be part of the table?

[vvira1316]

Hi Sunny,

No count is not Count(DISTINCT DIM_FLD1_Id). I'm attaching the table using Max Date that I've. I'm expecting count of 640. ResultSet1.xlsx

Yes for the second part clarification.

[sunny_talwar · Accepted solution]

For 3rd req... add a calculated dimension

=Aggr(If(Max([Data Date]) <= Today() - 30, DIM_FLD1_Id), DIM_FLD1_Id)

and uncheck 'Include null values' under the dimension

or you can use a set analysis for all your expressions

{<DIM_FLD1_Id = {"=Max([Data Date]) <= Today() - 30"}>}

For count... you can try this

=Count(DISTINCT {<DIM_FLD1_Id = {"=Max([Data Date]) <= Today() - 30"}>} DIM_FLD1_Id)

[sunny_talwar]

Try this... count where Data Date is not null (or is available)

=Count(DISTINCT {<[Data Date] = {"*"}>} DIM_FLD1_Id)

[vvira1316]

Hi Sunny,

Thanks for all your help.

I'll check and update.

BR,

Vijay