One possibility is the following: Define calculated field "rank" on Week In Qtr as RANK(MIN([Week In Qtr]), 'desc'), and then create a filter using "rank" as range (1, 2) (this gives you only the top 2 values from the column).
I'm attaching a sample workbook.
Hope this answers your question - if not, please let me know your exact desired output, and I'll try to help.
rank.twbx 5.0 KB
Thank you for your reply.
The complexity I have is, I do not want to filter data as other fields are showing quarter to date values and we cannot filter week.
Is there any formula which gives us the value of max week minus one value.
For example, according to date 07/27, max week is 04/2016. Now, one minus 04/2016, is 03/2016, i want that value which is 200.
Any idea how we can achieve this..
thank you for your time..
I see. Try playing with those functions (create calculated fields with those definitions):
DATEPART('week', today()) - this will give you a value for the current week with weeks starting on Monday.
If you want your calculation to be based starting on Jan 1 of the current year, try setting the start of week in DATEPART as follows.
First, calculate the day of week for Jan 1 of the current year as a calculated field named DayOfWeekJan1 and definition
datename('weekday', dateparse('yyyy-mm-dd', STR(datepart('year', today()))+"-01-01"))
Second, create a parameter off of DayOfWeekJan1 (right-click, create -> parameter)
Third, define calculated field named weekday with definition DATEPART('week', TODAY(), [dayofweekJan1 Parameter]).
Oh I see you have week in quarter, not week in year. You may have to calculate the difference with the start of the quarter, and divide by 7.
E.g. -DATEDIFF('week', today(), dateparse('yyyyMMdd', STR(YEAR(today()))+'0701'))
(you may have to do some if-then-else logic to calculate the start of the quarter that is hardcoded as '0701' above)
This may be the calculation you need:
-DATEDIFF('week', today(), dateparse('yyyyMMdd', STR(YEAR(today()))
+(IF MONTH(today())<10 THEN '0' ELSE '' END)
Thank you for your time. I tried your formula but was not able to get the desired result.
I have attached a sample workbook. I want help in creating a formula which would give me Sales number in the latest week in quarter, that is 04/2016 in Q316 and the value is 100. And also the prior week which is 03/2016 in Q316 where the value is 400. can you please help take a look..
again, thank you for your time.
WeekInQtrData.twbx 26.0 KB
I'd like to understand the following: What would you like to do with the 2 values you need a formula for? Display them, or use them in further calculations?
If the former, I think the solution I first proposed (using rank() function) serves the purpose. If the latter, we'll need to come up with a formula based on your end result.
I don't think it's possible to store these values in intermediate parameters for example (so you can use them further as new requirements arise). The formula that I gave you would only give you the index value, but you can't use it to look up another column: the LOOKUP() function is a table calculation and works only on entire tables, not on particular rows.
So, if you tell me your ultimate goal, I may be able to recommend something.
I was able to find a way to look up a column based on an index column in another thread: How to show the corresponded date of a certain value.
I'm attaching a solution using this idea. It uses the formulas for computing the ultimate and penultimate quarters as I suggested above, but it needs to start from a presumed 'date of report'. If you know the date of the report will always be today's date, you can replace references to [Date of Report] with today().
The lookups that I'm doing are the following:
IF [Quarter] = [LatestQuarter]
AND [Week in Quarter] = (IF LEN([LatestWeekInQuarter]) < 7 THEN '0' ELSE '' END) + [LatestWeekInQuarter]
THEN [Sales] END
IF [Quarter] = [LatestQuarter]
AND [Week in Quarter] = (IF LEN([PreLatestWeekInQuarter]) < 7 THEN '0' ELSE '' END) + [PreLatestWeekInQuarter]
THEN [Sales] END
Then I dragged the calculated fields to the chart area on a new sheet.
You can play with the DateOfReport parameter to see how your values change.
This doesn't take into account the case where the 2 weeks you need reported are straddling a quarter boundary (e.g. Q2 week 13 and Q3 week 1). You will need some if-then cases in the definitions to cover this case.
WeekInQtrData.twbx 27.6 KB
works like a charm. you are a genius!! this solves what my original requirement is.
Please try to look at the below complexity to the same requirement if you have some time or I can create a separate thread.
Now, that we got latest week sales and pre-latest week sales, I would like to show pre-latest week sales till Wednesday of every week and then on every Thursday I need to change it to latest week sales.
any idea on how we can get this..
thank you again for your time and patience in this regard. you have been very helpful.
If I understand your question correctly, all you need to do is add a calculated field like this:
IF (DATEPART('weekday', [DateOfReport]) <= 4) THEN [PenUltimateWeekSales] ELSE [UltimateWeekSales] END
4 is for Wednesday (Sunday is 1, Monday - 2 etc).
Again, if you need this to be based off of today's date, just replace DateOfReport with today().
WeekInQtrData.twbx 27.8 KB