Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count where NOT in a dimension but is in the previous dimension

Hi All,

I have attached an example data set.  What I want to do is for each week, count distinct customers who were NOT present in the week of the dimension but WERE in the previous dimension.

I am hoping to do this using set analysis with the use of no aggr or if statements as these are computationally restrictive for large data sets.

Does anyone have any good ways to do this?  The problem obviously is how to count people who by definition are not present in that week. 

I can't use the ABOVE or BELOW functions because I want to count distinct people based on Customer number.

Cheers!

GPC

12 Replies
sunilkumarqv
Specialist II
Specialist II

Its very simple add one more Visit_Date dimension to the table.

Hope this is what you looking for find the file and check it.

Not applicable
Author

Try Like this,

count({<Year=, Month=, Customer= E({<YearMonth={"$(=monthname(max(YearMonth)-1))"}>}), YearMonth={"$(=monthname(max(YearMonth)))"} >}distinct Customer)

Thanks,

Selva

Not applicable
Author

Sorry Sunil - I think you've misunderstood my question.

Not applicable
Author

Hi Selva,

I have tried something along these lines for testing:

concat({<Customer= P({1<Month_D={"$(=monthname(addmonths(max(Month_D), -1)))"}>} Customer )>} distinct Customer, ',')

I just want to check what customers are coming through using the concat rather than a count.  Makes it easier for proving the concept.

Here I am trying to have the customers who transacted in the previous month listed against the dimension of month.  The problem I'm having is that the max(Month_D) here goes to the 'overall max', so I'm getting Mar 2013 -1 = Feb 2013.  So I'm getting 'those customers from February who transacted in the month of the dimension'.

Any help would be greatly appreciated!

Cheers,

GPC

Not applicable
Author

I have attached another example here.  Same data but with that expression in there and what I want in the text box.

Cheers!

GPC

Not applicable
Author

Just use the above function around your existing current month function.

=above(concat( distinct Customer, ','))

The reason that set analysis won't work for this is because you are currently using the month dimension that you are trying to sort with as a dimension within the graph.

Not applicable
Author

Thanks, unfortunately the Above function won't work with customers when I want to count distinct or to include/exclude based on their behaviour in the past/future.  I understand the problem with the dimension, but this was exactly what I was trying to overcome with Set Analysis.

Is it simply impossible?

G

Not applicable
Author

Hi Gareth,

Unfortunately it is impossible to overcome with set analysis. If this is something that is important then you can always work around this by using a for loop in your script (and consolidate the last months customers into a new field), that way you can create a few artificial fields that will not be excluded by your use of the month as the dimension.

Cheers,

Tyler

Christian_Lauritzen
Partner - Creator II
Partner - Creator II

Tricky one.. Should be possible, but I haven't got to it yet. Suggest to start by adding WeekNum as a numeric counter for Week_D, also using WeekNum as main dimension. 

Suggested Customer Expression:

=Concat( Distinct {<Customer = - P(Total {<WeekNum={"$(=WeekNum-1)" }>} Customer)>} Customer,'.')

Still, as it does not work, those of you claiming this is impossible might be right

Would love to see a set analysis solution to this one, preferably without making any alterations to the original data structure.

Christian

Email: christian.lauritzen@b3.se