Mismatched Aggregation Error with DenseRank

Q&A
, , ,
1

I’m getting an error for Mismatched Aggregations, I’m not sure how to fix it. Basically I want to go through all my records that have Scenario = ‘Forecast’ and find the latest one by year then month. What’s the best way to fix this

ifelse(
    {SCENARIO} = 'FORECAST'
    AND denseRank(
        [
            {PLAN_FORECAST_YEAR} DESC,
            {PLAN_FORECAST_ACTUAL_MONTH} DESC
        ]
    ) = 1,
    1,
    0
)
2

Hello @leungjoot

Welcome back to Quick Sight Community!

Regarding your use-case, I have been looking into a workaround which can help. So for my test dataset, I used Segment column (your case ‘Scenario’ column). When ‘Segment’ Column is ‘Enterprise’, and latest year and latest month is ranked as 1, then it will be 1 else 0.

Firstly, I have extracted year and month as my dataset has date column.

year= extract(‘YYYY’,{Order Date})

month = extract(‘MM’,{Order Date})

Then for ranking, we have to use separate Calculated fields as to get latest year and month through ranking,

rank-test-month =
denseRank(
[max(month) DESC]
)

rank-test-year =
denseRank(
[max(year) DESC]
)

For numeric counterpart of String field, you can use similar to something below:

Segment-numeric
ifelse(Segment=‘Enterprise’,1,2)

You can write like ifelse({Scenario}=‘FORECAST’,1,2)

This numeric conversion was required to avoid mismatched aggregation in Calculated fields. I used min() to match aggregation with rank()
So, now my final Calculated field looks like:

ifelse(
min({Segment-numeric})= 1 AND {rank-test-year} = 1 AND {rank-test-month} = 1,
1, 0)

screen-shot-possible-workaround
screen-shot-possible-workaround2200×1348 417 KB

Hope this helps.

Have a nice day!!

3

Hi @leungjoot,

Following up here as it’s been awhile since we last heard from you on this thread; did you have any additional questions regarding your initial post or did the solution provided above help with your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

4

Hi @leungjoot,

Since we have not heard back further, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you