7 Replies Latest reply on Sep 21, 2016 4:34 AM by Samuel Gee

# Embarrassingly Easy Question

I have [Week] in my dataset as a dimension. It's an industry-standard value rather than a date, so in the format 1601, 1602, 1603 etc.

I have [Sales], also.

I'd just like to say show me the sales for the latest week, which will always be the highest value in the dimension.

I think something like:

IF [Week] = MAX[Week] THEN SUM[Sales]

But I'm mixing aggregate and non-aggregate in an IF. I tried getting rid the [Week] and just saying

IF MAX[Week] THEN SUM[Sales]

Any help on the calculated field text?

Thank you!

• ###### 1. Re: Embarrassingly Easy Question

You could just put [Week] = {MAX([Week])} on the filters shelf and set to 'True'

Alternatively a longer version could be IF [Week] = {FIXED: MAX([Week])} THEN [Sales] END

• ###### 2. Re: Embarrassingly Easy Question

Hi Samuel.

I think its a great question. Actually this is most frequently used option /techniques when it comes to analytics.

There are many ways through which you can achieve the same. I am explaining in terms of Superset dataset,You can replace the date Field based on you requirement.

a) Approach one:

1) create a Boolean Variable using the following syntax

max([Order Date]) = window_max(max([Order Date]))

you can replace order date with your week dimension,

2) Drag the above Boolean into filter, then select "True".

b)Approach two:

DATENAME('week',{MAX([Order Date])})

will give you the week of the maximum date.

c) Approach three:

IF [Week] = {FIXED: MAX([Week])} THEN [Sales] END

d) Approach Four

By using custom SQL (Sub query technique inside the Query)

SELECT [Sheet1\$].[Date] AS [Date],

[Sheet1\$].[Value] AS [Value],

[MaxDate].[Date] AS [Latest Date],

(SELECT MAX([Sheet1\$].[Date]) AS [Date] FROM [Sheet1\$]) AS [MaxDate]

FROM [Sheet1\$], (SELECT MAX([Sheet1\$].[Date]) AS [Date] FROM [Sheet1\$]) AS [MaxDate]

Hope this Helps

1 of 1 people found this helpful
• ###### 3. Re: Embarrassingly Easy Question

Thank you ever so much Rajeev!

• ###### 4. Re: Embarrassingly Easy Question

Thank you Andrew

• ###### 5. Re: Embarrassingly Easy Question

Hi Samuel,

if it solves the issue,could you please mark my answer as correct answer so others should also get benefited

• ###### 6. Re: Embarrassingly Easy Question

This is especially surprising to me - I wasn't aware you could jump straight to the measure when using LOD. All my previous expressions have been FIXED Dimension:AGGREGATE(Measure).

• ###### 7. Re: Embarrassingly Easy Question

Hi Rajeev,

I marked Andrew as correct only because he commented before you! I trust that's okay? Thank you ever so much for your help.

Sam