Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (3)
12 Replies
sunilkumarqv
Valued Contributor II

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

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

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

Try Like this,

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

Thanks,

Selva

Not applicable

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

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

Not applicable

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

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

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

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

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

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

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

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

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

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

christianlaurit
Contributor II

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

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

Community Browser