Track changes in user list per month

[OysteinT]

Hi

We have a list of users we have a manual excel in which we track changes based on a couple of criterias:

- Added from last month (not present in last month's list)
- Deleted in this month (present in last month's list but not this month)
- Changed Manager (compared to last month)
- Changed department (compared to last month)
- Changed phoneNr (compared to last month)

In excel these are just lots of vlookups with copy-paste into a report sheet, but it should be able to automate in QS... 

Ideally I'd like each changed parameter to generate a line in the changed-table so it's easy to build a reporting visualization. But thats just luxery. 

also my example result table might not be best set ut, feel free to change it. A change can happen several times over a users timeline which is why I was thinking one line per period at least. 

Also, if I see how one of the changes in dept/manager/phone I can figure out the others. 

(the example file is simple but covers the basics, our full files has 15k users each month). 

[Qlik1_User1]

Try this if it helps

1.Create a table with old data and a table with new data.

2. Join them (left or inner ), depends on you data.

3. compare the column values and flag them like If(Phn_1=Phn_2,1,0) flagPhone.

4. Use this flag to proceed further.

[OysteinT]

Hmmm

not sure how that would work, espesially since one of the comparisons/visuals are if the user is new, or has been deleted. 

And since I'll be adding new data each month .

What I could do is some sort of key_identifying, adding the period/month to the ID tag to generate an line per month and some kind of comparison there.

Outcome would be something like this in the new/merged table

ID_Period	ID	Department	Period	Year	Month	Add	Remove	ChangeDpt
1_202001	1	111	2020-01-31	2020	01		1
2_202001	2	222	2020-01-31	2020	01
3_202001	3	333	2020-01-31	2020	01
2_202002	2	111	2020-02-29	2020	02			1
6_202002	6	333	2020-02-29	2020	02	1

Where you look up the value on the previous month (or next in case of removals) to see what it is. But how to get that right?

The key is easy enough to make - just add a new table with something like, and the year + month can be calculated from the period with YEAR() and MONTH(), I've just put them in the table as visual aid.

load ID&'_'&Year&Month as ID_Period

 
What kind of extra load statements can I then make to do the checks?

For both Added and Removed I think a simple IF(EXISTS) type  check on the previous (or next) months ID is present (with a check on removed that if it is in the current month there is no new file yet to check against) should be enough.

Same with the other - some kind of LOOKUP/Match in an IF() to check the previous months value, and if it it is changed set it to 1 else null? 

[Rodj]

Everything you need should be calculable using set expressions without any need to create new tables.

For example, to identify newly added records you would have a measure such as the following :

if( //using an If statement to convert our count into a text value flag
Count( // we're going to count how many IDs meet the set condition
{$< [Period.autoCalendar.MonthsAgo] = {0}, // For the set of records existing in the current month
ID = E({1<[Period.autoCalendar.MonthsAgo] = {1}>}) // excluding those IDs that existed in the previous month
>}
ID) = 1, 'New', 'Old') // if we get a count then flag the record as new

Easiest way will be to have a separate table for each calculation initially. I've assumed that ID is unique and that you are happy using the auto-generated date dimension type fields such as "MonthsAgo". They do make things like this simpler.

Cheers,

Rod

[Rodj]

Sorry for the crappy formatting, it looks better like this:

[OysteinT]

Hey

ohhh, that looks good. I've gotten the Added and Removed bits working now it seems. (I've checked now, and both Added and Removed are spot on now when I checked the last two months reports in the manual process).

Set Expressions are not something I've dived much into as of yet, and am wrapping my head around how they work.

But the logic for tracking changes should be about the same I imagine, but only adding an extra dimension into the check? 

[Rodj]

That's right, shouldn't be much different.

When it comes to wrapping your head around set expressions, don't get too caught up in the syntax at first. It is basic set theory with a limited set of logic, if you work out the logic first and then work back to the syntax it makes it easier to keep what you are doing straight in your head. The implicit modifiers 'P' and 'E' are often overlooked as a way of defining the set logic because people get wrapped up in the other syntax.

[OysteinT]

I need an excel to Qlik-translator 🙄

Logic is simple:

if Department(ID:MonthsAgo(2)) not equal Department(ID:monthago(1)) then ChangedDept=1 else ''

Which I would think might be something like this:

if (
{$ < [Period.autoCalendar.MonthsAgo]={1}, CoorID, CostCenter>} = {1<[Period.autoCalendar.MonthsAgo] = {2}, CoorID, CostCenter>}
,'ChangedDept','')

Which of course is not an valid measure syntax. 

[Rodj]

So in this case we are comparing the value of Department for two different sets. There's probably a few ways of doing this.

Assuming a unique identifier for a person is our dimension the following should work:

           if(Only({$ <MonthsAgo = {1}>} Department) <>  Only({$ <MonthsAgo = {2}>} Department), 'Moved', 'Stayed')

I've used "Only" because set expressions can only be used in an aggregation, and Only() is a handy way of aggregating the "only" value.

Thinking in terms of set logic though, what we are after is the set of values for one month that excludes the set of values for another month, so we should be able to use set operators ('-' in this case) to do this as well:

if(

Count({$<MonthsAgo = {1}> -$<MonthsAgo = {2}>} ID)

= 1, 'Moved', 'Stayed')

The above assumes you have person ID and Dept as dimensions in your table/chart. Set expressions operate over the dimensions in the data set you've defined. Note I haven't tried this with your data so it's my fault if it doesn't work. 

[OysteinT]

Hi

thanks again. If I want a filter on each month to see the changes over time, I assume I need to change my measures to be more dynamic, as it now is fixed it seems. 

But the Department change only shows "stayed" when I tried it 😞 

I've added a couple more months of data to get more to test-functionality. Updated app is attached.