Set analysis to find new records

[#1]

I have an application where I have a supplier database (covering many regions, countries etc). Every month some new suppliers are added and I would like to present a table with the new suppliers based on the selections of the users. I thought this would be an easy thing to do with set analysis but I am struggling to get it working.

Iv'e tried many different versions, below are two examples. But none is working as I hope (they should show a 1 for all new suppliers and 0 for all the other ones). In the example below, month# 26 is the current month and monht# 25 the previous one. I want it to be dynamic (I will replace the hard coded numbers with variables later on) to allow the user to choose different time periods, but first thing is to get the basic expressions working.

With the second expression I seem to be getting all suppliers apart from the ones in month 25 and the first I don't know what I'm getting...

Count( DISTINCT {$ <SpendMonth#={26} > - <SpendMonth#={25} >} [Supplier number])

Count( DISTINCT {$ <SpendMonth#={26} ,SpendMonth#=-{25} >} [Supplier number])

As a second step, I aim to list the suppliers in a calculated dimension instead, using the below set in something like this:

=aggr( if(count( {$< SpendMonth# = {26}> - <SpendMonth# ={25} >} [Supplier number] )>0, [Supplier number]), [Supplier number])

Any suggestions?

[Oleg_Troyansky]

In your second expression, the operator "=-" is incorrect, and therefore the SA expression is ignored (hence all suppliers).

Logically, you need to subtract suppliers that existed before the current month, from all suppliers. Something like this should work:

Count( DISTINCT {$ <SpendMonth#={"<=26"} > - $<SpendMonth#={"<26"} >} [Supplier number])

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

[#3]

Oleg,

Thanks for your reply. Actually, the second expression seems to give me all suppliers in the last month AND all suppliers in all other months (I can see the logic in it, that the comma is acting as a union between the two different sets, although I am a little bit uncomfortable with that behaviour).

I tried your suggestion but it did not work (it works fine if I would take it in two different expressions, i.e. two different counts and subtract them, but I would like to have one expression).

[Oleg_Troyansky · Accepted solution]

Sorry, I wasn't thinking clear when I was formulating your formula. Of course, you need your Set Analysis expression to filter Suppliers and not Months. Something like this (can't guarantee the exact syntax):

Count( DISTINCT {$ <

          [Supplier number] = P({<SpendMonth#={"<=26"}>} [Supplier number])-

                                          P({<SpendMonth#={"<26"}>} [Supplier number]) 

                              >} [Supplier number])

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

[#5]

Thanks, that worked! Just had to change it to only show changes over the last month (i.e. not <26 but =25).

A follow-up question - the aggregation also worked (just have to change the expression to the one provided). But I actually want to show the supplier name and not the number. That also works fine, but I have one problem. And that is that supplier names can change over time and I only want to show the current name.

For instance, let's say that a supplier with supplier number 123 today is called BBB but used to be called AAA. When I use the aggregation below, it will show two lines, one for the old name and one for the new name. Is there any way to only show the new name?

=aggr( if(     "<your expression >"       >0  , [Supplier name]), [Supplier name])

[Anonymous]

Hello Oleg,

your solution gave me clue formy problem. In my case suppliers (to keept the wording) are leaving and new suppliers are joining. Using a straight table I am able to display suppliers in a dimension and show which suppliers changed.

What I need is on a monthly base, how many suppliers left and how many suppliers new joined in.

Do you have an idea if that is possible. In the straight table the expression for new suppliers would be like this (those leaving is similiar). I used an inline code for testing purposes.

if

(count({<Datum={'$(=vDatum2)'}>}Dim)=1 and count({<Datum={'$(=vDatum1)'}>}Dim)<>1,1,Null

())

Any ideas are greatly appreciated.

Thanks

Rudolf

[Oleg_Troyansky]

I'd recommend to add this logic in the load script and set flags for "new" and "leaving" suppliers in each month. Then, you can simply use the two flags in your set analysis and show the counts month by month. Otherwise, it gets very tangled, and you can't use month-driven set analysis in a monthly chart (because Set Analysis is only performed once per chart).

cheers,

Oleg Troyansky

[#8]

I ended up adding adding an extra selection for the last month in the expression within the set analysis (SpendMonth#={26}). That solved the issue to only show the supplier names that I was interested in. I had an idea to add the supplier names as an expression and using maxstring and select only the latest record, but since it is not possible to do any selections in an expression, I did not like that solution. Adding it in the load script felt like a too complex operation (suppliers may show up and then disappear for a few months and then reappear - did not know how to do that in a smart way).

And to make it a little bit easier for the user, I actually added supplier name as an extra dimension and hid the aggregated one. The reason is that the "green dot" that shows that a selection had been done, did not show up in the aggregated dimension.

=aggr(
if(
Count( DISTINCT {$ < SpendMonth#={26} , SupplierNumber= P({<SpendMonth#={26}>} SupplierNumber)-
P({<SpendMonth#={25}>} SupplierNumber)>} SupplierNumber)
>0, [Supplier name]), [Supplier name])

Again, thanks for all the help!

[#9]

Hi Rudolf,

Using this expression as a calculated dimension will give you all the suppliers that have joined (mark suppress when null). Set an expression as 1 and you will have the list of the ones that have joined (I haven't tried the syntax, so may be something wrong in it).

=aggr(
if(
Count( DISTINCT {$ < Dim= P({<Datum={'$(=vDatum2)'}>} Dim)-
P({<Datum={'$(=vDatum1)'}>} Dim)>} Dim)
>0,Dim),Dim)

If you instead are just looking to get the number, you can just used the middle part and put it in a text box. That will give you the correct amount.

Count( DISTINCT {$ < Dim= P({<Datum={'$(=vDatum2)'}>} Dim)-
P({<Datum={'$(=vDatum1)'}>} Dim)>} Dim)

[Anonymous]

looks good in my small test version

Now i will use it in my larger customer project!