@danielreyes3rd Hi Daniel - in the 2nd paragraph it explains it. It is a parameter so the user can change the date the MTD, YTD etc calcs are based from. Otherwise if you always want today’s date then use the function now().
Hi @Jesse, thanks for sharing this.
I tried creating MTD, which worked absolutely fine. I was trying to do ‘Last week’ and ‘WTD’ but not getting the desired results. I referred to last two weeks formula to create last week but no luck. Can you please guide, thanks
Hi @prkgupta , if you adjust the <=2 part it should work.
Last Week:
ifelse(dateDiff({order_date},now(), "WK") =1, sales,0)
WTD:
ifelse(dateDiff({order_date},now(), "WK") =0, sales,0)
Note that SPICE and many DBs treat Sun as start of the week. Custom start of the week is coming soon. If you need custom week start sooner you can write additional calcs to adjust. WOuld need to run some ifelse’s off of calcs like these (e.g. last week is date <= this date minus 7 days and > this date minus 14 days)
https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Custom-Week-Start-Day
Hi @Jesse,
thanks for your kind response. I have already tried these variations. I have tried both now() and maxdate. Both results differ in case of week and none of them gives the correct result.
using the above formula for WTD, is giving me same result as MTD. My monthly and yearly figures are correct but somehow week figures aren’t.
Sunday is meant to be the start of the week. Your help and guidance in this matter will be highly appreciated, thanks.
Hi @Jesse, thank you for the useful post.
I have a table grouped by MM/YYYY (as a field), and calculates servers count for each month. I need to calculate a custom MoM%, comparing current servers count of each row, to servers count of each row of max month of previous year.
Table sample is below.
I have tried this function:
periodOverPeriodPercentDifference(count({sample_day}), {sample_day}, MONTH, extract('MM',{sample_day}))
with dynamic offset, but getting an error for the offset (saying it does not have a correct type, even though it returns an integer)
@prkgupta If you were using now() and this screenshot was from this week, maybe the MTD and WTD numbers are the same since there was only 1 week so far in Sep?
Hi @ariebe - can you try moving the extract(‘MM’,{sample_day}), assuming that is your offset, to it’s own calc and then reference that field in here instead? Maybe we can get past the syntax check that way.
Otherwise try writing a calc like this:
count(ifelse(month=maxOver(month, [extract(‘YYYY’, month)], PRE_AGG), serverID, null), [extract(‘YYYY’, month)])
Then do another one which does the % change
(count(serverID)-sum(previousCalc))/sum(previousCalc)
@Jesse Its not just WTD, none of the Week related figures match up. Last week, 2 weeks before, etc.
Yes there has been only one week in September but 1st and 2nd of September were in previous week. This week commenced 3rd Sept.
I have tried both MaxDate and now(), both give different figures and both incorrect, but only when i am trying to get weekly figures…
Hi there @Jesse, Thanks for this very helpful article.
I’m struggling with “This Week” filtering:
I got this dataset where each account has a daily value of “blocks”
I’m trying to build a week-over-week comparison.
I’m using ifelse(dateDiff({calc_date},now(),‘WK’)=1,{block_count},0) to filter only values from last week, but I’m getting a “0” value for all dates regardless of which week they are on.
When changing it to 'WK"=0 I get the value in ‘block_count’ for all the dates, no matter what week they are on.
What am I missing?
Thanks, Yuval
Hi @Yuval.Shiboli and @prkgupta - appears we introduced a bug (UPDATE, not a bug, I just misunderstood expected behavior of this function at WK level) in the WK period logic in dateDiff function for SPICE. Use these instead (I have updated article).
This week:
ifelse(truncDate('WK', {order date})= truncDate('WK', now()), sales, 0)
Last week:
ifelse(truncDate('WK', {order date})= addDateTime(-1, 'WK', truncDate('WK', now())), sales, 0)
@Yuval.Shiboli , @prkgupta - I learned that dateDiff at WK period behaves differently than the other periods (like month or year) since it is a known length (7 days) whereas months can be 28-31 days and years can be 365-366. Therefore the dateDiff at WK level measures how many actual days difference from the end date in the calc, and then floors it to nearest integer (does not use Sun-Sat weeks, just pure number of days diff). For example if today is Wed then dateDiff(datefield, now(), ‘WK’) will be 0 for all days from last Thu to today (not just from this past Sun to today), and also includes future dates up to 6 days in future. I guess I never noticed this, my fault!
I have updated the article to use the new formulae from my last comment specifically for Weeks (this week and last week) using truncDate instead. All the other periods can still use the dateDiff logic.
To add on, this (which is what I had previously in the article for ‘This Week’):
dateDiff({order date},now(),'WK') = 0
Is basically the same as this:
dateDiff({order date},now(),'DD') < 7 and dateDiff({order date},now(),'DD') > -7
@prkgupta hmm do you have any comments after it? The calc editor likes comments to only be at the top (not at the bottom). Maybe you can DM me a screenshot. Seems to have worked for Yuval.
Hi @Jesse, it worked perfectly fine now.
Do you know if we can also get ‘Last Week to Date’ (as in same days last week as this week) just like month and year?
Thanks for the update @Jesse . So, it is more of a ‘last 7 days’ than a ‘last week’ calculation.
makes sense!
@prkgupta Great! To only take same days of last week (aka Last WTD, vs full last week) you can add one more condition. Ill add it to the article too.
Last WTD:
ifelse(truncDate(‘WK’, {order date})= addDateTime(-1, ‘WK’, truncDate(‘WK’, now())) AND {order date}<=addDateTime(-1, ‘WK’, now()), sales, 0)
1 Like
Perfect! thank you!
All working good, I just replaced now() with MaxDate to get accurate figures. Thats may be due to refresh rate of data.
2 Likes
Hi, My user wants WTD calculation from Monday to Today instead of Starting from Sunday. How do I achieve this?
Hi @lopa_5555 - see this example in DemoCentral. Also stayed tuned in a couple weeks there will be native support for changing the start day of the week, and calcs like truncDate will inherit the custom weekday start.
1 Like