Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a formula where my denominator needs to be the number of days in the week, UNLESS it is the current week and then it needs to be number of days less 1 (so that it does not count the current day).
Seems pretty straight forward... but can't get it going.
I thought I could use the {1} in the set expression and say "If the max week number (ignoring selection) = the selected week number, count the days less 1, if not, count the days."
If(({<[WeekEndWk#]={"1(=Max(WeekEndWk#))"}>}
=
{<[WeekEndWk#]={"$(=Max(WeekEndWk#))"}>})
,
(Count(Distinct(Date)))-1
,
(Count(Distinct(Date))))
Any suggestions on correcting syntax or an easier way?
Thanks!
Thank you all for your help!
I got a formula that accomplishes what I was looking for. May not be the prettiest per se... but it works!
If([WeekEndWk#]=Week(Today()),
((Num(Today()) - Num(Date(WeekStartDate)))),
((Num(Date(WeekEndDate))) - (Num(Date(WeekStartDate))))+1)
Please share your data or app.
Thank you!
Hi Steph, maybe with:
If([WeekEndWk#]=Max({1} [WeekEndWk#])
,
(Count(Distinct(Date)))-1
,
(Count(Distinct(Date))))
Ah so close!
I think this is on the right track, still giving me zeroes, but better than just null.
Attached is a screenshot with some sample info - for week 9 (not the current week) I would want the formula to pick up on 7 days worth of sales. But in the current week (even though there's been 3 days since today is Tuesday) I only want it to count the two days that have sales, whereas the current day count is 3.
Hope this helps!
May be this, What exactly you are counting? No of days for each SiteID/Week#?
If([WeekEndWk#]=Max({1} [WeekEndWk#])
,
Aggr(Count(Distinct(Date)))-1,SiteID,[WeekEndWk#])
,
Aggr((Count(Distinct(Date)))),SiteID,[WeekEndWk#])
Thereare many possibilities on what if failing, maybe the easiest way is to load a flag in calendar table (or where date field is):
LOAD date
If(Floor(date)=today(), 1, 0) as isToday
...
and use:
Count(Distinct {<isToday={0}>} Date)
Edit: forgot to add that similar flags can be added to get max week or other needs.
Thank you all for your help!
I got a formula that accomplishes what I was looking for. May not be the prettiest per se... but it works!
If([WeekEndWk#]=Week(Today()),
((Num(Today()) - Num(Date(WeekStartDate)))),
((Num(Date(WeekEndDate))) - (Num(Date(WeekStartDate))))+1)
Please close the thread by marking any helpful and a correct answer