Set Analysis issue

[sibin_jacob]

Hi All,

I need a help on the below set analysis.

I have two columns, one is SALES_DATE and another PURCHASE_DATE.

User will select the PURCHASE_DATE in the dashboard

I need to show the sales in the table of which date is selected by the user.

AGGR(only({<SALES_DATE=P(PURCHASE_DATE)>} SALES_DATE), SALES_DATE)

I am using the above formula for the dimension, which is working fine if I have sales date for the Purchase date user selected

For Example : User selected 17/01/2017 in the Purchase date list box, but I don’t have sales in that date so I need to show the near previous sales data in the table, that is 15/01/2017

Thanks,

Sibin Jacob. C

[Anonymous]

Something like??

AGGR(only({<SALES_DATE={="max(if(SALES_DATE<PURCHASE_DATE,PURCHASE_DATE))"}>} SALES_DATE), SALES_DATE)

[sibin_jacob]

Hi Siva,

It is not working.

If user selecting 17/01/2017 in the PURCHASE_DATE listbox, which is not present in SALES_DATE.

SO I need the near previous date from SALES_DATE column.

I need Max(SALES_DATE),considering the selected PURCHASE Date

Thanks,

Sibin Jacob. C

[Anonymous]

Sorry Sibin, Can you try this??

aggr(Date(MAx(if(SalesDate<PurchaseDAte,SalesDate)),'DD/MM/YYYY'),PurchaseDAte)

[Anonymous]

=date(aggr(max(if(SALES_DATE<=PURCHASE_DATE,SALES_DATE)),PURCHASE_DATE
//put additional Dimensions here like ,Customer,Article...if needed
))