Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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?
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
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?
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.
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?
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.
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)
Try this... count where Data Date is not null (or is available)
=Count(DISTINCT {<[Data Date] = {"*"}>} DIM_FLD1_Id)
Hi Sunny,
Thanks for all your help.
I'll check and update.
BR,
Vijay