Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Something like??
AGGR(only({<SALES_DATE={="max(if(SALES_DATE<PURCHASE_DATE,PURCHASE_DATE))"}>} SALES_DATE), SALES_DATE)
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
Sorry Sibin, Can you try this??
aggr(Date(MAx(if(SalesDate<PurchaseDAte,SalesDate)),'DD/MM/YYYY'),PurchaseDAte)
=date(aggr(max(if(SALES_DATE<=PURCHASE_DATE,SALES_DATE)),PURCHASE_DATE
//put additional Dimensions here like ,Customer,Article...if needed
))