Calculation of sum or why doesn't my second expression work?

[Anonymous]

Hello,

I'm trying to calculate the sum of sales by store where client's last date <= today()-10 (an example data is below).

Data:

ClientID	Date	Sales	Product	Store
1	2018-01-05	100	1	Store_2
1	2018-01-05	200	2	Store_2

Problem:

I've created two expressions but the results are different. The second expression doesn't work correctly

Expression 1:  sum( {<Date={'<=$(=max( TOTAL {< ClientID=>} today() )- 10)'}>} Sales)

Expression 2:  sum( aggr(if( max( TOTAL <ClientID>  Date ) <= max( TOTAL {< ClientID=>} today() )- 10 , Sales , 0 ),  ClientID,Date))

Why doesn't the second expression work? (Could you explain and make him work)

With best regards,

Evgeniy

[Anil_Babu_Samineni · Accepted solution]

Could be date format Issue? Perhaps this

sum( aggr(if( max( TOTAL <ClientID>  Date ) <= Date(max( TOTAL {< ClientID=>} today() )- 10,'YYYY-MM-DD') , Sum(Sales) , 0 ),  ClientID,Date))

[Anonymous]

Thanks for the response.

There aren't problems in date's format, but when you've changed Sales to sum(Sales) it's it's probably solved the problem.

sum( aggr(if( max( TOTAL <ClientID>  Date ) <= max( TOTAL {< ClientID=>} today() )- 10 , sum(Sales) , 0 ),  ClientID,Date))

Why do we need an additional aggregation for the "Sales" field inside?

[Anil_Babu_Samineni]

The only one thing we need to understand the definition of Aggregation inside?

Definition - Sum can calculate Cell Wrap level where Field calculated from Row Level. Example

Name, Sales

A, 10

A, 20

B, 20

C, 30

C, 40

Output will get when use Sales Returns all rows while Sum(Sales) returns

Name, Sales

A, 30

B, 20

C, 70

Does this helps?

[Anonymous]

Thank you so much!

I've got confused myself.

With best regards,

Evgeniy