Accumulated Bar Chart With 2 dimensions not working

[vdcastro]

Hello everyone,

I have a bar chart with the evolution of sales by month for Current Year and Prior Year with the following logic:

Dim1: Month

Dim2: =ValueList('PY','CY')

Measure:

PICK(MATCH(ValueList('PY','CY'),'PY','CY'),

//PY
num(
(
Sum({<
Year={$(vMaxPriorYear)},
MonthID={"<=$(vMaxPriorMonth)"}>}
Sales
,
//CY
num(
(
Sum({<
Ano={$(vMaxYear)},
MesID={"<=$(vMaxMonth)"}>}
Sales
)
)
/1000000 ,'#.###,# M€')
)

And it works fine.

Now I want to create the evolution of sales accumulated by month for Current Year and Prior Year:

I want use the same dimensions and I used this measure:

PICK(MATCH(ValueList('PY','CY'),'PY','CY'),

//PREMIOS PY 

aggr(rangesum(above(

Sum({<
Ano={$(vMaxPriorYear)},
MesID={"<=$(vMaxPriorMonth)"}>}
Sales
)

,0,RowNo())),Month)

,
//PREMIOS CY

aggr(rangesum(above(

Sum({< 
Ano={$(vMaxYear)},
MesID={"<=$(vMaxMonth)"}>}
Sales
)

,0,RowNo())),Mpnth)

)

I don't understand the result because the expression is not returning values to current year. I think I have a problem with the aggregation by the dimension CY/PY

Can someone help me with this problem?

Thank you,

best regards

[sunny_talwar · Accepted solution]

Try this expression

PICK(MATCH(ValueList('PY','CY'),'PY','CY'),

//PREMIOS PY 

Aggr(RangeSum(Above(

Sum({<
Ano={$(vMaxPriorYear)},
MesID={"<=$(vMaxPriorMonth)"}>}
Sales
)

, 0, RowNo())), Month)

,
//PREMIOS CY

Aggr(NODISTINCT RangeSum(Above(

Sum({< 
Ano={$(vMaxYear)},
MesID={"<=$(vMaxMonth)"}>}
Sales
)

, 0, RowNo())), Month)

)

[vdcastro]

It works.
Thank you very much 🙂
Can you explain to me the impact of the "NODISTINCT" only on the 2nd part of the expression?

[sunny_talwar]

Read it here 🙂

AGGR() and Synthetic Dimensions