I have a table which contains two columns, week and snapshot-day. the column snapshot day is basically a date, ex: 2022-11-18. the week is just the week number, however not from the snapshotday, but from the other data within the column.
in my table, I have a snapshot day of, say, today, and from todays snapshot day I have weeks 45, 46 and 47. in the attached foto you can see the snapshotday, some other columns and week.
now, I want to be able to filter by the lowest week within the same snapshot_day. so, for 2022-11-15, I want to see a table only with values from week 47, and not week48.
Not sure I am doing correctly what you said, but it does not seem to work.
Let me illustrate my issue better:
1- In the visualization you see snapshot day and week.
2- I filter the snapshot day to show all days of the current week. With that, if today is the 25 of November, then my table will show nov21, nov22, nov23, nov24 and nov25.
3- Here is the trick: I need this week filter to be equal to the week of the snapshot day
For now I have to filter the week manually, otherwise I will see values of wk47 from snapshotday = 14 nov, 15 nov, and so on as well, which means I will have week 46 and 47 and thus the values in the table will add up.
Now the question is: how to make filter week = week of snapshot day? I do have a column “snapshot week”, which means I would only need to equal week = snapshot week. If I could write something like “where week = max(snapshot-week)” that would do the job.
I would make a calculated field that is an ifelse that says is your week field equal to this field ^. If so return ‘True’ else return ‘False’ and then filter only for trues.
thanks, I actually found a different way, I find the date measures in QS a little messy.
Logic is similar, but I added the logic in the table itself. column “filter_test”: if “week” = “snapshot_week”, then “week” else 0.
then I filtered week based on top bottom filter, top of “week” based on max of “filter_test”
