How to do nested nested aggregation

[kiara]

Hi friends , I am new to qlik. please help.

I want to add 1 more condition in my if statement with aggregation function and that is a calculated field.

I need to add add 1 more condition in the following expression :

 min(if([Service.Service Code Raw] = 'H0031' or [Service.Service Code Raw] = '90791',[Service Payor.Date of Service]))

Condition to add:

(max(if([Service.Service Code Raw] = 'H0046' ,[Service Payor.Date of Service]))) <= (min(if([Service.Service Code Raw] = 'H0031' or [Service.Service Code Raw] = '90791',[Service Payor.Date of Service])))

[kiara]

@sunny_talwar  Do you have any suggestions. I noticed you in every post you have wonderful skills. 

[sidhiq91]

@kiara  May be you can do a Resident load to satisfy the above condition.

Temp:

Load

min(if([Service.Service Code Raw] = 'H0031' or [Service.Service Code Raw] = '90791',[Service Payor.Date of Service])) as Min_Service

From SourceTable;

Noconcatenate

Temp1:

Load 

max(if([Service.Service Code Raw] = 'H0046' ,[Service Payor.Date of Service]<=Min_Service,'your condition')) as Max_service

Resident Temp;

Drop table Temp;

[kiara]

@sidhiq91  Thank you dear. But I don't have qlik sense . I can only write expression in the qlik view.

[vinieme12]

Hi ,

Please add some context what exactly are you trying to evaluate/create here? 

are you creating a Measure / Dimension in a chart  ??

 or creating a new field during data load??

You can combine multiple value criteria's in set analysis as below

=  min({<[Service.Service Code Raw] = {'H0031' , '90791'} >} [Service Payor.Date of Service])

= if(  max({<[Service.Service Code Raw] = {'H0046'} >} [Service Payor.Date of Service])

<=  min({<[Service.Service Code Raw] = {'H0031' , '90791'} >} [Service Payor.Date of Service])

, SomeValue , ElseValue )

some info about how your data is structured/sample data / screenshots /mockups outputs about expected output make it easier for members here to help you

[kiara]

Actually these are measures in the table. In these expressions I am trying to get the maximum or minimum date of service a  based on the service code . 

Like in this expression min(if([Service.Service Code Raw] = 'H0031' or [Service.Service Code Raw] = '90791',[Service Payor.Date of Service]))

I am getting the min of date of service for codes h0031 and 90791. But for some codes I want that "date of service should be greater than date of service of  Hoo31 and 90791.

I hope it gives some clarity.

All these are calculated fields that's why I am getting difficulty with aggregated functions.