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?

[Anil_Babu_Samineni]

Perhaps this for bold expression?

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

[ioannagr]

Hey @Anil_Babu_Samineni ,No, just "-"  presented 😕

[Kushal_Chawda]

@ioannagr  Try below

Dimensions:

1) Client

2) aggr(max([visitdate]),[Client])  

Expression:

sum({<[Availabity]={'available'}>} [amount purchased])

[ioannagr]

Hi @Kushal_Chawda , still nothing. It looks like it doesn't take into account the max(visitdate), shows the total of available items this client has ever purchased

[Kushal_Chawda]

@ioannagr  try below

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

[ioannagr]

@Kushal_Chawda  now it gives me 0's everywhere 😕 Do you perhaps have another idea?

[ioannagr]

@Kushal_Chawda @Anil_Babu_Samineni let me explain to you what is happening.

I do a straight table with columns, client, aggr(max([visitdate]),[Client])  and then if I try  aggr(sum( {<[date={ '$(= max([date]))' },[availability]={'available'} >} [amount purchased]),[client])

This strange thing happens : For only one client I get the correct sum for their max(visitdate) and for the rest I get "-"s.

BUT if i click on another client's name (that also has a different max date) , the "- " changes to the expected number.

What is this now? 

[tresesco]

@ioannagr , Try like:

Dimensions:

1) Client

Expression:

1) date(max([visitdate])) 

2) FirstSortedValue({<[Availabity]={'available'}>} Aggr(sum({<[Availabity]={'available'}>} [amount purchased]), Client) , - [visitdate])

Or,

FirstSortedValue({<[Availabity]={'available'}>} Aggr(sum( [amount purchased]), Client) , - [visitdate])

[ioannagr]

Hello @tresesco , just "-"s everywhere. 😞

When you mention dimension you mean not a master item dimension, just select dimension instead of measure in the straight table, right?