—VISUALISATION
Vertical bar chart as below.
x-axis = product
value = qty (Sum)
group = year
Small Multiples = brand
Filter on year=paramYear, month=paramMonth, brand=paramBrand, rankproduct<=paramTopN
The chart is working perfectly, showing Top N products under selected brands in the selected year.
—NEW REQUIREMENT
User got new requirement to include bars to show qty in year-1.
Any idea if this is possible.
OR other ways to achieve same objective? Possibly changing the data model?
my first question is: Do the users want to see the TopN products from year 0 with the quantity from year -1?
As I see it, you need to add the paramYear filter condition to your rankproduct calculation. Then you extend your year=paramYear filter with an OR statement that also allows year=paramYear-1.
Users want to see TopN products based on paramYear. Chart to show 2 bars for the TopN products, 1 bar is qty in paramYear and another bar is qty in paramYear-1.
“add the paramYear filter condition to your rankproduct calculation” - how to do this?
“extend year=paramYear filter with an OR statement that also allows year=paramYear-1” - This is clear.
the idea sounded good in my head. I tried to implement it using an example to show you. Since neither mixing aggregated and non-aggregated values nor nesting two aggregations are allowed, I unfortunately don’t see a solution right now. Sorry for getting your hopes up.
I’ve been trying for a long time.
If no one else has any ideas, these would be good feature requests:
Alternative mixing of aggregated and non-aggregated fields
Hi @lbl,
I agree with Nico, with the current system limitations, I don’t believe this will be possible to setup as you’ll most likely begin encountering mismatched aggregations. I would suggest building in a prior year calculation to your dataset so that you’re not filtering out that information through your filters.
I can mark this as a feature request to promote visibility to the support team.