Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

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:

ClientIDDateSalesProductStore
12018-01-051001Store_2
12018-01-052002Store_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

1 Solution

Accepted Solutions

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))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
4 Replies

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))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable
Author

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?

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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable
Author

Thank you so much!

I've got confused myself.

With best regards,

Evgeniy

Community Browser