topic Re: Calculation of sum or why doesn't my second expression work? in QlikView

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

Anonymous — Thu, 01 Feb 2018 14:27:26 GMT

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 — Thu, 01 Feb 2018 14:35:21 GMT

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 — Thu, 01 Feb 2018 14:49:16 GMT

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 — Thu, 01 Feb 2018 14:53:10 GMT

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 — Thu, 01 Feb 2018 14:59:45 GMT

Thank you so much!

I've got confused myself.

With best regards,

Evgeniy