max data

[Krish2459_58]

Hi,

We have to show only the max Area Charge Effective Date in the staright table by area reference.Please check the attched sample.

Property Code	Area Reference	Area Type	Area Charge Effective Date	Rentable Area	Area Measure
702006	25000511	Retail	01/12/2021	195.2	SQM
702006	25000832	Carpark	01/12/2021	2	Carparks
702006	25000511	Retail	24/10/2018	195.2	SQM
702006	25000832	Carpark	24/10/2018	2	Carparks

required output:

Property Code	Area Reference	Area Type	Area Charge Effective Date	Rentable Area	Area Measure
702006	25000511	Retail	01/12/2021	195.2	SQM
702006	25000832	Carpark	01/12/2021	2	Carparks

Thanks..

[Sandhuuuu]

Hi, 

You need to add a Measure to your table and then put a MAX aggregation on it, e.g.  MAX(Area Charge Effective Date).

This will reduce the [Area Charge Effective Date] to only show the Maximum value. 

If you put this as a Dimension it will appear as an 'Invalid Dimension' as you cannot do an Aggregation in a Dimension.

Hope this helps,

Jordan

[Bhushan_Mahajan]

@Krish2459_58 Hi you can use below solution.

ABC:
Load *,Date("Area Charge Effective Date",'DD/MM/YYYY') as Date;
load * Inline [
Property Code,Area Reference,Area Type,Area Charge Effective Date,Rentable Area,Area Measure
702006,25000511,Retail,44531,195.2,SQM
702006,25000832,Carpark,44531,2,Carparks
702006,25000511,Retail,43397,195.2,SQM
702006,25000832,Carpark,43397,2,Carparks
];

left join
CDE:
Load "Property Code", Date(Max(Date),'DD/MM/YYYY') as Date_Max
Resident ABC
group by "Property Code";