Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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) )
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) )
Read it here 🙂