topic Re: Set analysis to get expected output in QlikView

Set analysis to get expected output

vvira1316 — Mon, 23 Jul 2018 12:29:49 GMT

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

Re: Set analysis to get expected output

sunny_talwar — Mon, 23 Jul 2018 12:40:08 GMT

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?

Re: Set analysis to get expected output

vvira1316 — Mon, 23 Jul 2018 13:21:01 GMT

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

Re: Set analysis to get expected output

sunny_talwar — Mon, 23 Jul 2018 13:23:24 GMT

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?

Re: Set analysis to get expected output

vvira1316 — Mon, 23 Jul 2018 13:38:33 GMT

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.

Re: Set analysis to get expected output

sunny_talwar — Mon, 23 Jul 2018 13:44:31 GMT

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?

Re: Set analysis to get expected output

vvira1316 — Mon, 23 Jul 2018 13:52:03 GMT

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.

Re: Set analysis to get expected output

sunny_talwar — Mon, 23 Jul 2018 13:52:30 GMT

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)

Re: Set analysis to get expected output

sunny_talwar — Mon, 23 Jul 2018 13:55:53 GMT

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

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

Re: Set analysis to get expected output

vvira1316 — Mon, 23 Jul 2018 13:56:20 GMT

Hi Sunny,

Thanks for all your help.

I'll check and update.

BR,

Vijay

Re: Set analysis to get expected output

vvira1316 — Mon, 23 Jul 2018 15:29:25 GMT

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

Re: Set analysis to get expected output

sunny_talwar — Mon, 23 Jul 2018 15:31:56 GMT

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)

Re: Set analysis to get expected output

vishsaggi — Mon, 23 Jul 2018 15:33:49 GMT

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))

Re: Set analysis to get expected output

vvira1316 — Mon, 23 Jul 2018 15:39:04 GMT

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

Re: Set analysis to get expected output

vvira1316 — Mon, 23 Jul 2018 15:39:33 GMT

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

Re: Set analysis to get expected output

sunny_talwar — Mon, 23 Jul 2018 15:40:11 GMT

Sounds good

Re: Set analysis to get expected output

vvira1316 — Mon, 23 Jul 2018 15:52:01 GMT

Hi,

It did help. Thanks a lot.

BR,

Vijay

Re: Set analysis to get expected output

sunny_talwar — Mon, 23 Jul 2018 15:54:35 GMT

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.