1. New Date =Dateadd('quarter',-1,[Order Date])
2. find last month of New Date per Order Date Quarter ( something like Fixed Year ( Order Date), Quarter ( Order Date) : Max( Month(New Date))
3. if last month = Month (Order Date) then [Sales]
4. Sales + if last month ( with Quarters on Dimension)
I haven't tried it, but this is how I would do it.
Is this what you are thinking? The sum of each quarter's sales, plus the last month of the previous quarter. I'm sure there's many ways to handle this, but this is the first that came to mind for me:
If so, I've used fixed LODs to calculate the the quarterly total, and the last month of the current quarter's total, then used LOOKUP to identify the previous quarters last month, then summed the current quarter's total and last quarter's last month.
Does this help?
Quarter+Month_possible.twbx 459.6 KB
Thanks Dan. Close, but not quite.
I was looking for last month of the previous quarter. Also, is there a way to do this without the month being shown in the view ? The view should only have year, and quarter.
Ah, alas. I showed the columns to demonstrate the steps I'd gone through, they're not required. One of the calculation fields is "Last Quarter's Last Month" or something similar, that's the field you're looking for. Does this help?
1 of 1 people found this helpful
Hi Lazy Rambler,
I created some table calcs and then hid a couple of them in the final view so all you see is the year, quarter, and the current quarter sales plus the sales of the final month of the previous quarter.
First I put the quarter of the order date on the rows shelf and the sum of sales as a measure value on the text mark.
I then created the Last Month Value field to calculate the sum of sales for the last month of each quarter:
if datediff('month',datetrunc('quarter',[Order Date]),datetrunc('month',[Order Date])) = 2 then [Sales] end
I placed that field on the measure values shelf as well. I then created the "Current Q + Prev Month Sales" table calc to add the sum of sales for the current quarter to the Last Month Value field. This returns the two values added together for every quarter, except for the first quarter it's just returning the sum of sales since there's not a previous quarter:
ifnull(sum([Sales])+LOOKUP(sum([Last Month Value]),-1), sum([Sales]))
I put that on the measure values shelf and then right clicked on the column headers for both sum(sales) and sum(last month value) and clicked "Hide" so that they're still included in the calculations, but you can't see them in the view.
This is all in the attached workbook. Let me know if you have any questions.
Thanks Paul. This is really helpful.
The only thing now is, when I sort the quarter in descending order, I seem to lose the calculation. If I change the look up calculation to LOOKUP(sum([Last Month value]),1), it seems to work.
Let me know if that is correct.
Right, that's exactly how to do that. Since you're sorted in descending order, you want to lookup the last month value from the next row instead of the previous.
I have run into another problem now. I only need 8 quarters of data to be shown in my view. Using this calculation, I applied a logic to only show 8 quarters. The logic being :
DATEDIFF('quarter',[order date],[Date param])<=7 AND
DATEDIFF('quarter',[order Date],[Date param])>=0
The problem now comes with the last quarter. As this needs the last month of the 9th quarter to calculate, and because I'm applying the 8 quarter filter, the last quarter is not showing me the correct value.
Any thoughts ?
simple fix for that - on you filter, include all 9 quarters. Then, on the 9th quarter that you don't want to appear in your viz, right click on the quarter and select "Hide". This will keep it as part of calculating your viz but won't show anymore.
Yes, I had tried it earlier. Changed the logic to show 9 quarters, and then hid the last quarter, but the thing is, For example, say my parameter has Jun 2018 selected, then my view will be like this :
And now, I hid 2016 Q1, and now my view looks like this :
But, now when I select Dec 2017, then because I hid the 2016 Q1 earlier, it is now not showing in my view. Hope that makes sense.
hmm... yea I see the issue, but can't think of a simple solution. You need that last quarter for the calculation and you don't want it to show, but that last quarter will change based on the value of the parameter.
Since you already closed this thread out before, I'd recommend creating a new one to push it back to the top of the content list. I'll keep thinking about it, but in the meantime someone else may have an answer too.