Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
I have a amount column with transaction date.
I want to see sum amount of trailing 8 weeks using the transaction date and from the saturday of today's date.
for example today is 3/1/2023, I want to see trailing 8 weeks of sales from 3/4/2023 and back to 8 weeks.
any one has a handy set analysis for that.
If you use the "Create Calendar Measure" wizard, Qlik Sense will autogenerate some master items for you
You can play with them choosing which weeks you want to include.
It will be something like
Sum( {< [DateField.autoCalendar.WeeksAgo]={"<=8"} > } [Value] )
thank you. I am trying to do this in set analysis as I am dealing with multiple dates with various expression.
I am assuming your datefield in Number Format.
Try this:
Sum({< DateField={"=$(=Floor(Num(weekend(Max(DateField)),-56)))"}>}Amount)
If you DateField is in Date format try this:
Sum({< DateField={"=$(=Date(Floor(Num(weekend(Max(DateField)),-56))))"}>}Amount)
That´s the "trick", instead of using a hard to maintain date expression you can use the derived week field to make you life easier
Sum( {< [DateField.autoCalendar.WeeksAgo]={"<=8"} > } [Value] )
Means "sum all values from previous 8 weeks"