Filter only on Weekends or Weekdays
I have a need to filter based on day of week - for example, I need to total all weekend (or weekday) sales.
The filter would seem to be the way to go, but I can't find a way to get it to filter based on weekday. I tried JAQL exclude, which it parsed ok but didn't seem to work correctly.
Any other ideas?
pb_si
Posted 1 year ago·Last reply 1 year ago
7 comments
Jake Raz
·1 year agoHello! I asked about this topic in a different thread, and someone from Sisense responded with a script that might work. See here for their answer. Hope this helps!
Jake Raz
·1 year agoOne way to do this, without needing to make changes in the data model, is to calculate the day directly from the date. There's various ways you can derive the day of the week from a given date. For example, see here (I don't think that's the only one, I saw some others while searching).
With a bit of experimentation you can figure out how to convert the algorithm into the equivalent formula(s) in Sisense and create a Measured Value that will output the number of the day of the week (e.g. Sunday is 0, Monday is 1, Tuesday is 2, etc). Then you can filter based on that.
Jake Raz
·1 year agoHmm, actually, I'm not sure how to do with this formulas. You'd need to somehow output numbers based on the date values, and I can't find a way to do that. For example, if we're trying to find the year. In the formula editor, none of the listed formulas will return a year number (e.g. "2024") from a given date. I found a page in the Sisense documentation that mentions a "GETYEAR" function, but evidently that only works if you're modifying the data model; if you try to use the same formula in the front-end, it just doesn't work (errors out). Another thing I thought might work would be to use the YDIFF formula to calculate the year difference between a set date (say, 1/1/2000) and the target date field. However, there's no way to enter a static date in the formula, it only wants to accept a date value from a field of some sort.
Another thing to consider would be using a widget script to calculate everything instead. However, I'm not sure how to do that. Sorry!
pb_si
OP1 year agoThanks Jake_Raz, this was my first choice - really, I'd prefer to do this in UI layer to avoid adding maintenance in the model, but I'm not sure it's possible without changes to formula interpreter (unless anyone knows different!)