Error..

[nikhilgarg]

Hey,

I ahve a doubt that what is the difference between the following 2 expressions?

1: if(Date >= vRefernceDate, sum(Sales1))

2: Sum(If(Date >= vReferenceDate, Sales1))

In both above cases, for Date() i have by default Only(). Now, in my attached application, for ID 6, i have 2 different dates, then

why i am getting sumofsales2 value in last column ?

Thanks

[maxgro]

i think yes for 1, no for 2

for 2 there is no need of an aggr function because the if is at the row level

also Sum(If(only(Date) >= vReferenceDate, Sales1)) seems incorrect because of 2 aggr functions

maybe hic‌

can help us to better understand

[hic]

• If(Date >= vRefernceDate, sum(Sales1))

This will be evaluated once per value in the dimension. Since Date is a naked field reference, "Only(Date)" will be used instead of "Date"

• Sum(If(Date >= vReferenceDate, Sales1))

In this expression the If() function will be evaluated once per record in the source data, so an aggregation function in it would make no sense and is not possible.

HIC

[nikhilgarg]

Hey ,

Thanks HIC but it my doubt may sound stupid but i ma not able to understand why

• If(Date >= vRefernceDate, sum(Sales1))

This will be evaluated once per value in the dimension ?

• Sum(If(Date >= vReferenceDate, Sales1))

In this expression the If() function will be evaluated once per record in the source data ?

I mean how can we tell that any expression will run once per value in dimension or once per record ?

Thanks

[hic · Accepted solution]

This is defined by the aggregation function. An aggregation function is not like other functions. An aggregation function is like a For-Next loop - looping over all records in the raw data. So, whatever is outside the aggregation function will be evaluated once per dimensional value; and whatever is inside will (in addition) be evaluated on row-level. Think of it as two loops, one nested in the other.

.

Below I have marked the aggregation functions in blue, and the expression inside the aggregation function in light blue.

If(Only(Date) >= vRefernceDate, Sum(Sales1))

Sum(If(Date >= vReferenceDate, Sales1))

See Aggregations and Function Classes

HIC

[nikhilgarg]

Hey,

Thanks for the explaination HIC.

But, If whatever we write inside aggregation function is to be evaluated at row level then,

For: If(Only(Date) >= vRefernceDate, Sum(Sales1))

Date should be evaluated at row level instead at dimensional level becoz it is under aggregate function Only().

Please correct me if i ma wrong.

Thanks

[hic]

The Only(Date) function is like a For-Next loop: It loops over all the rows, and for each row it checks the value of Date. But an aggregation function returns only one value per dimensional value - also if there are many input rows. The Only(Date) function returns Date if all rows have the same value, and NULL otherwise.

HIC