Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to do calculations based on a date selection and I cant figure it out how to write the set analysis
I want to calculate the sum of PickQty based on the date selection in the master calendar.
%DateKey is equal to ExpectedShipDate due to the point of view (what is due on the selected date)
Basically I want to see: (assuming selection on %DateKey is today)
1. what have I done in the past that was actually due today
2. what have I done today that was due today
3. what have I done today that was due in the future
for example:
1. sum(PickQty) where PickEvent = WORKLOAD and "PickDate smaller than selected %Datekey"
2. sum(PickQty) where PickEvent = WORKLOAD and "PickDate equal to selected %Datekey"
3. sum(PickQty) where PickEvent = WORKLOAD and "ExpectedShipDate larger than selected %Datekey and PickDate equal to selected %Datekey"
One issue is the date format.
%DateKey is a regular date format but the others, PickDate and ExpectedShipDate are YYYYMMDD
I tried using a variable to work around but this makes the syntax in the set analysis even harder for me.
any help is apreciated.
attached a sample QVW with what I have tried so far.
Lucas
I'll play a little with the MasterCalendar to find out what you mean.
Thanks for your effort!
I am simply trying to pull the quanity oh hand value on a past date for an item.
I am trying sum({$<[ItemHistory.Txn Date]={"<=2020-01-31"}>}[ItemHistory.Quantity On Hand]) but it is not returning the correct quantity and does not change values with a change of date.
Do you have thoughts on a solution?