Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a cases:
table: Month, Region, Product, ProductClass, Sale
Dimension could be: Month, Region, Product, ProductClass
Measure: sum(Sale)
Then: I want to make a measure to calculate accumulated sum(sale) Year-to-month, base on selection of users on month and other dimensions like Region, Product, productclass. Then, a measure like:
(sum({1<Month={"<=$(=max(Month))"}>} Sale
The problem is {1} will ignore all user's filter on all dimensions, {$} will make make the accumulated calculation not work , it's calculated on selected month only.
I want to make {1} impact on Month dimension only, how can I achieve that?
I'm using KPI chart.
Thanks for any help.
You could try combining a full set and p() like this
Sum(1{<[SomeField]=P(SomeField), AnotherField = P(AnotherField) Amount)
My guess works be that you are having other selections besides Month that is reducing your set, eg. YearMonth, Week or Date.
Try cancelling these by modifiers in your set like this :
sum({$<YearMonth=, Week=, Date=, Month={"<=$(=max(Month))"}>} Sale)
Great idea, I'll try it.
Just wonder, if I have many dimensions, and just want user do selection on some specific dimensions and ignore others, it's not good to list all ignored dimension in set analytics, is there any other solutions?
Thanks so much.
You could try combining a full set and p() like this
Sum(1{<[SomeField]=P(SomeField), AnotherField = P(AnotherField) Amount)
Cool, it work perfectly.
I tried it before but not work. But my last solution has a different:
Sum(1{<[SomeField]=P(SomeField) + AnotherField = P(AnotherField) Amount)
what different between ',' and '+' in this case?
Thanks @Vegar
I'm glad it worked out for you!
I'm not sure what the expression using + is trying to do. The syntax is to use comma between each modifier. You can use plus to add different sets on each other, but the syntax is slightly different.