The last fact for the current selection (help to make expression)

[asgardd2]

Hello!

     I need help to make expression in my chart.

The example application and source file in the attachment.

I have transaction table (Sales) and catalog - "AKB" ( in this table i can see bind to the client - manager, depending on the date.)

     When i choose '01.01.2016' in the date , i want see sales for same day from the  last year - 01.01.2015( in the second column of the chart "Fact in the last period (-1 Year) " ) , considering the binding manager-client of the current sample.

So , for example on manager "Ivan" i want see last year sales  only for clents: number1,number2,number3,number4,number5.(his current binds client - manager, in date 01.01.2016)

Thank you!

Best regards,

Alexandrov Anton.

[sunny_talwar]

Is this the output you are hoping for?

Sum({<clientOfManager = {'X'}, Date = {"$(=Date(AddYears(Max(Date), -1)))"}>}Sales)

[asgardd2]

Sorry, i have a mistake in my description.

So , for example on manager "Ivan" i want see last year sales  only for clents: number1,number2,number3,number4,number5.(his current binds client - manager, in date 01.01.2016)

In your solution :

But in expression "Fact in the last period (-1 year)" i would like see 66+94 = 160 (Because in current period (01.01.2016) he does not have bind with client "number9")

[sunny_talwar · Accepted solution]

Try this:

Sum({<clientOfManager = {'X'}, Date = {"$(=Date(AddYears(Max(Date), -1)))"}>}

Aggr(If(Only(TOTAL <Client> {<Date = {"$(=Date(AddYears(Max(Date), -1)))"}>}Client) = Only(TOTAL <Client> {<Date = {"$(=Date(AddYears(Max(Date), 0)))"}>}Client),

   Only({<Date = {"$(=Date(AddYears(Max(Date), -1)))"}>}Sales)), Manager, Client, Date))

[asgardd2]

Thank you for your solution!

It is work fine then i have one or more binds in previous period on manager from current period. 

But sometimes i do not have binds in previous period.For example, then i deleted binds rows :

i see zero value in previous period:

But i have sales values on this clients ... and i would like see value = 160 in this row.

[asgardd2]

    I changed data model (add new type of flags clientOfManager  - ""cur_period" and "last_period"), and then i added this condition in set-expression.  This solution shows correct values.

[sunny_talwar]

Flags are always a good option. I am glad you were able to figure it out