Aggr takes at least 2 parameters

[eprehu]

Hello, 

I need help, i don't know where is the pb here  : 

THanks.

[eprehu]

THnaks, 

I want to do the same thing but with 2 conditions 

I need to have 30276 but i have 17 436. 

Here the calculation : 

[rubenmarin]

Maybe you need another dimension, in that table where 30276 is shown, which are the dimensions of that table? use the same dimensions in the outside aggr()

Basically if you have a table with [Expression], to do the sum row by row you need: Sum(Aggr([Expression],[TableDimension1],[TableDimension2]...[TableDimensionX]))

[eprehu]

Here, the  calculation where 30276 is shown : 

The customer key is the good dimension to use 

[rubenmarin]

Not really, that expression on a table can return 30276 because it's calcualted row by row and the sum of all rows returns 30276. When using the same expression outside the table you need to aadd the aggr with the dimensions of the table.

Open the table properties and check the dimensions used on the table, that should be the parameters of the aggr

[eprehu]

I don't understand because the calculation are the same, i only changed the variables of time var_Date_Sel_LY and var_Date_Sel_LLY

Maybe it is a problem because of the AND?

[rubenmarin]

First try this:

Basically if you have a table with [Expression], to do the sum row by row you need: Sum(Aggr([Expression],[TableDimension1],[TableDimension2]...[TableDimensionX]))

[eprehu]

Customer repeat 12M :

// Repeat
sum({<[Report Date]>}
if(
aggr({<[Report Date]>}
mod(
sum(
{<
[Report Date], $(var_Reporting_Currency), [@QV_GIV.SOURCE]={'Sales'},
$(var_Customer_Begin_Date_Saleforce)={"<=$(var_Date_Sel)"}, $(var_Customer_End_Date_Saleforce)={">=$(var_Date_Sel_LY)"},
[Customer Last Purchase Date]={">=$(var_Date_Sel_LY) <=$(var_Date_Sel)"}//,
//[Customer First Purchase Date]={">=$(var_Date_Sel_LY) <=$(var_Date_Sel)"},
//[Customer Creation Date]={">=$(var_Date_Sel_LY) <=$(var_Date_Sel)"}
>}
[Recruited Flag]
),
2
)=0,
[Customer Key]
)
and
aggr({<[Report Date]>}
if(
sum(
{<
[Report Date], $(var_Reporting_Currency), [@QV_GIV.SOURCE]={'Sales'},
$(var_Customer_Begin_Date_Saleforce)={"<=$(var_Date_Sel)"}, $(var_Customer_End_Date_Saleforce)={">=$(var_Date_Sel_LY)"},
[Customer Last Purchase Date]={">=$(var_Date_Sel_LY) <=$(var_Date_Sel)"}//,
//[Customer First Purchase Date]={">=$(var_Date_Sel_LY) <=$(var_Date_Sel)"},
//[Customer Creation Date]={">=$(var_Date_Sel_LY) <=$(var_Date_Sel)"}
>}
[Recruited Flag])>0,
1,
0
),
[Customer Key]
),
1,
0
)
)

12-24m

count(distinct
aggr(nodistinct {<[Report Date]>}
If(
sum(
{<
[Report Date], $(var_Reporting_Currency), [@QV_GIV.SOURCE]={'Sales'} ,
$(var_Customer_Begin_Date_Saleforce)={"<=$(var_Date_Sel_LY)"}, $(var_Customer_End_Date_Saleforce)={">=$(var_Date_Sel_LLY)"},
[Customer Last Purchase Date]={">=$(var_Date_Sel_LLY) <=$(var_Date_Sel_LY)"}
>}
[Recruited Flag]
)>0
and
mod(
sum(
{<
[Report Date], $(var_Reporting_Currency), [@QV_GIV.SOURCE]={'Sales'},
$(var_Customer_Begin_Date_Saleforce)={"<=$(var_Date_Sel_LY)"}, $(var_Customer_End_Date_Saleforce)={">=$(var_Date_Sel_LLY)"},
[Customer Last Purchase Date]={">=$(var_Date_Sel_LLY) <=$(var_Date_Sel_LY)"}
>}
[Recruited Flag]
),
2
)=0,
[Customer Key]
),
[Customer Key]
))

in the if calculation : 

If( $(_customerRepeat12Months) = 1
AND
(
count(distinct
aggr(nodistinct {<[Report Date]>}
If(
sum(
{<
[Report Date], $(var_Reporting_Currency), [@QV_GIV.SOURCE]={'Sales'} ,
$(var_Customer_Begin_Date_Saleforce)={"<=$(var_Date_Sel_LY)"}, $(var_Customer_End_Date_Saleforce)={">=$(var_Date_Sel_LLY)"},
[Customer Last Purchase Date]={">=$(var_Date_Sel_LLY) <=$(var_Date_Sel_LY)"}
>}
[Recruited Flag]
)>0
and
mod(
sum(
{<
[Report Date], $(var_Reporting_Currency), [@QV_GIV.SOURCE]={'Sales'},
$(var_Customer_Begin_Date_Saleforce)={"<=$(var_Date_Sel_LY)"}, $(var_Customer_End_Date_Saleforce)={">=$(var_Date_Sel_LLY)"},
[Customer Last Purchase Date]={">=$(var_Date_Sel_LLY) <=$(var_Date_Sel_LY)"}
>}
[Recruited Flag]
),
2
)=0,
[Customer Key]
),
[Customer Key]
)
))=0,
1, 0
)

[eprehu]

in the if calculation i have 0 and i want to 30276

[eprehu]

If i do this from the if calculation (add sum and aggr and dim) : 

sum(aggr(
If( $(_customerRepeat12Months) = 1
AND
(
count(distinct
aggr(nodistinct {<[Report Date]>}
If(
sum(
{<
[Report Date], $(var_Reporting_Currency), [@QV_GIV.SOURCE]={'Sales'} ,
$(var_Customer_Begin_Date_Saleforce)={"<=$(var_Date_Sel_LY)"}, $(var_Customer_End_Date_Saleforce)={">=$(var_Date_Sel_LLY)"},
[Customer Last Purchase Date]={">=$(var_Date_Sel_LLY) <=$(var_Date_Sel_LY)"}
>}
[Recruited Flag]
)>0
and
mod(
sum(
{<
[Report Date], $(var_Reporting_Currency), [@QV_GIV.SOURCE]={'Sales'},
$(var_Customer_Begin_Date_Saleforce)={"<=$(var_Date_Sel_LY)"}, $(var_Customer_End_Date_Saleforce)={">=$(var_Date_Sel_LLY)"},
[Customer Last Purchase Date]={">=$(var_Date_Sel_LLY) <=$(var_Date_Sel_LY)"}
>}
[Recruited Flag]
),
2
)=0,
[Customer Key]
),
[Customer Key]
)
))=0,
1, 0
),[Customer Key]))

I have this result : 

[rubenmarin]

I would need to take a look at the app, if you can upload a sample app with sample data that reproduces the issue I can take a look.