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

[vvira1316]

Hi Sunny,

It is working the way expected. I've couple more questions and they are related to Geo Chart that I'm trying to put together. I can open another thread, if you would like me to do so. Please let me know.

The question is as follows

Question 1)

Is there a way to not display the country where count is 0. (Please refer to image for reference.)

Formula in the label is as follows

=CountryName & Chr(10) & Count(DISTINCT {<DIM_FLD1_Id = {"=Max([Data Date]) <= Today()"}>} DIM_FLD1_Id)

Question 2)

How do I get a count of remaining distinct DIM_FLD1_Id that are not part of 640 count.

BR,

Vijay

[sunny_talwar]

Hey Vijay -

I have no idea of how Geo Chart works buddy... I think it makes sense to open a new thread which will help you get better coverage.

2) you want total cound - 640? May be this

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

[vishsaggi]

May be you can use like below for Question one in your Label?

=IF(Count(DISTINCT {<DIM_FLD1_Id = {"=Max([Data Date]) <= Today()"}>} DIM_FLD1_Id) = 0, 0,

CountryName & Chr(10) & Count(DISTINCT {<DIM_FLD1_Id = {"=Max([Data Date]) <= Today()"}>} DIM_FLD1_Id))

[vvira1316]

Thanks, I'll try or clarify with you in the new thread.

[vvira1316]

Thanks. I'll open a new thread and close this one.

[sunny_talwar]

Sounds good

[vvira1316]

Hi,

It did help. Thanks a lot.

BR,

Vijay

[sunny_talwar]

Hey Vijay, feel free to mark helpful responses for any response which helped your reach your final goal. If vishsaggi‌'s helped you, I would encourage you to mark his response as helpful as it might help others in the future when they have similar issues.