Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables. One lists the transactions (8 days of data), one lists the transaction number with the date of the change.
I need to know if there was improvement since the change was made. The issue is, I need to compare the date of the week. Transactions on Monday are higher than a Sunday. Day of week is highly important in these transactions when comparing the data. Let's say today is the 7th (a Thursday). As I have only 8 days of data, I have data back to the 31st.
C38249 change was completed on Monday the 4th (at midnight). I have four days of data beginning the 1st, but I only have three days after. So I can only compare 3 days. Three days of week. The days after the change 4th, 5th, 6th (Monday, Tuesday, Wednesday). So I need to compare the Monday, Tuesday, and Wednesday before and after the change.
C45067 change was completed Tuesday the 5th. In this case, I can only compare 2 days, since I only have two days of data after the change. I need to compare Tuesday and Wednesday before and after the change.
Is this something QlikView can do thru set analysis?
**** EDIT ****
This data is only the transactions that have a change. This data, I am pulling in a separate QVD that says, hey, only pull in the data for the specified transactions that had a change in the second table. Only the data that is in this database had a change. I created separate QVDs for this. This is to compare the days of the week. How to I compare the previous Wednesday for the change to the following Wednesday for the change?
TransDate | ItemNumber | Success | DayOfWeek |
3/2/2024 | C45067 | 45 | Sat |
3/2/2024 | C56849 | 23 | Sat |
3/2/2024 | C38249 | 48 | Sat |
3/3/2024 | C45067 | 37 | Sun |
3/3/2024 | C56849 | 27 | Sun |
3/4/2024 | C38249 | 17 | Mon |
3/5/2024 | C56849 | 55 | Tue |
3/5/2024 | C38249 | 37 | Tue |
3/5/2024 | C23874 | 48 | Tue |
3/5/2024 | C45067 | 37 | Tue |
3/6/2024 | C23874 | 26 | Wed |
3/7/2024 | C56849 | 38 | Thu |
3/7/2024 | C45067 | 17 | Thu |
3/8/2024 | C38249 | 58 | Fri |
3/8/2024 | C56849 | 26 | Fri |
DateOfChange | ItemNumber |
3/4/2024 | C38249 |
3/5/2024 | C45067 |
Set analyses are evaluated on a chart and not line level. If you had a global change date you could use set analysis. Since your's needs to be avaluated per line, yoll have to use other means.
By far the easiest way would be to add a flag in the datamodel for all transactions that have occured after a change. That way you could use a set analysis on the flag to restrict the data to transactions after a change.
The best alternative I can come up with is using Aggr() to restrict the TransDate field to only those that occur after a change:
=Aggr(If(TransDate >= Only(DateOfChange), TransDate), TransDate, ItemNumber)