Help with set analysis aggr max

[ioannagr]

Hi a have a dimension field, let's call it 

Client and for each client i have a different max(visitdate)

I want to make a table to present the columns : client, max visitdate for each client and  for each max visit date for each client the amount of available items purchased so i did :

Client,      aggr(max([visitdate]),[Client])  ,   sum({<[visitdate]={"=aggr(max([visitdate],[Client]))" },[Availabity]={'available'}>} [amount purchased])

but the expression in bold is wrong!

What is it i do wrong?

[tresB]

@ioannagr ,

Yes. Can you confirm if your date field underlaying values are number, i.e. not strings?

[ioannagr]

@tresB from the qvd i'm loading the data i see that visitdate is 

Date(Floor(max(issue_date)),'DD/MM/YYYY') as [visitdate]

[Kushal_Chawda]

@ioannagr  expression I shared should work. Make sure that you replaced actual field names in that expression. Please share the sample app to look 

[ioannagr]

@Kushal_Chawda it shows me  0's which is not the expected answer.

I can't share sample data unfortunately but all my data are pretty much these.

Client,

Product,

Amount purchased,

visit date timestamp,

visit date,

WarehouseID

[Kushal_Chawda]

@ioannagr  you can share the screenshots of the data and expressions you are using otherwise I am afraid as we can't help you much on that without looking at data or sample app

[ioannagr]

@Kushal_Chawda can you give me some input on how i would convert this expression for the same date previous year?

For example if max visit date was 6/10/2020, i'm asking for the 6/10/2019 

[Kushal_Chawda]

@ioannagr  which expression you are using for current max date?

[ioannagr]

@Kushal_Chawda based on your suggestion sum({<[Availabity]={'available'}>}aggr(if(Date=max(total <Clinet> Date),Sales),Clinet, Date))

i think the bold gives the max date per client? i want the previous year's same date for each of clients

[Kushal_Chawda]

@ioannagr  try below for previous year.

sum({<[Availabity]={'available'}>}aggr(if(Date=addyears(max(total <Client> Date),-1),Sales),Client, Date))

But above expression I think will add second row for each client as previous year Date will be different. If you want single record then I think you need to have another aggr on top of previous expression like below

sum(aggr(sum({<[Availabity]={'available'}>}aggr(if(Date=addyears(max(total <Client> Date),-1),Sales),Client, Date)),Client))

or

sum(total <Client>{<[Availabity]={'available'}>}aggr(if(Date=addyears(max(total <Client> Date),-1),Sales),Client, Date))

Similarly you need to change the expression for current year and check "suppress zero value" option