3 Replies Latest reply on Aug 6, 2018 11:07 PM by Ryan Julius Banez

# Category associated with LOD function

Good day

I used the APAC superstore as test data for my inquiry. Suppose I want to know which Country originated the Maximum total sales per order last August 2015 (Answer is China).

I did LOD for Sum of Sales per Order ID: {FIXED [Order ID]: SUM([Sales])} and I can use the Month-Year filter to show only August 2015. Nothing difficult there except that it shows the number.

However I want it to show China instead of 6,607. Something similar to a VLookup.

I searched the forums and the closest I encountered is this tutorial --> Calculated Field (LOD) as Lookup Criteria

However, when I apply the same thing on my worksheet, it breaks down everything per country. I only want to show the country where the maximum sum of sale per order was computed

I would appreciate any help.

Thank you.

Ryan

• ###### 1. Re: Category associated with LOD function

Hi Ryan,

You can try below approach:

1. per order sales = ({FIXED [Order ID],DATETRUNC('month', [Order Date]): SUM([Sales])})

2.   max country name =if ({FIXED DATETRUNC('month', [Order Date]): max([per order sales])})=[per order sales] then [Country] end

1 of 1 people found this helpful
• ###### 2. Re: Category associated with LOD function

Hi,

Find my approach,

IF {FIXED DATETRUNC('month',[Order Date]):MAX({FIXED [Order ID],DATETRUNC('month',[Order Date]):SUM([Sales])})}

={FIXED [Order ID],DATETRUNC('month',[Order Date]):SUM([Sales])}

THEN [Country] END

Let us know if this help.

Mahfooj

1 of 1 people found this helpful
• ###### 3. Re: Category associated with LOD function

Meenu and Mahfooj,

Thank you both.

Both solutions worked well.

Ryan