3 Replies Latest reply on Mar 16, 2016 6:39 AM by Derrick Austin

# Display the most profitable product for each month

Hello guys =)

I would like to ask you do you know solution for the problem I haven't find yet.

In my database I have data for my fruits selling history. This is simple example:

idcategory
price
# of itemsTotal priceMonth
Date
1Bananas4,9914,99Jan01.01.2016
2Papaya3,9927,98Jan02.01.2016
3Bananas4,9939,98Jan02.01.2016
4Coconut4,4928,98Dec19.12.2015

In tableu I would like to make table like this:

CategoryMax Month
BananasSeptember (5847,69)
PapayaOctober (5183,5)
CoconutDecember (8,98)

I would like to display the top month for each fruit and value for this month.

I hope this can be done.

Thank you a lot. =)

Mislav

• ###### 1. Re: Display the most profitable product for each month

Hey Mislav,

You can definitely do this with LoD calculations. I've attached an example.

Basically, use one to find the Maximum total for each category, then compare that to each month to find the month that is the maximum.

Hope this helps!

- Derrick

1 of 1 people found this helpful
• ###### 2. Re: Display the most profitable product for each month

Hi Derrick,

Thank you for your answer, but still I dont get what I need. For Bananas value that I need to get is 4,99 + 9,98. I dont need max row. I need to get max month for which sum of total prices is max.

I would be very happy if you know solution. because If I try your approach :

{FIXED [Category] : MAX([Total Price])}

I tried to modify this to : {FIXED [Category] : MAX(SUM([Total Price]))} but then I got error:

---is aggregate function and cannot be further aggregated.

Thank you,

Mislav

• ###### 3. Re: Display the most profitable product for each month

Hey Osoba,

I didn't notice that "Month" and "Date" didn't line in the example. To work around this, you can nest another layer of LoD calculations to sum up the month before determining the max() month.

Attached an updated example.

1 of 1 people found this helpful