How to Count Dormant Members Qlikview 8.5

[#1]

Hi There,

I am trying to count all the members that haven't made a sale in the last 6 months.

I'm guessing it would be something like this: count({$<~YearMonth = {'201007','201006','201005','201004','201003','201002'}>}distinct MemberNo)

but i am trying to automate it so that i do not have to manually change the expression every time 1 month passes.

Help would be much appreciated.

[Miguel_Angel_Baeyens]

Hello Richard,

Fo this kind of things I use "flags" meaning that when I create the master calendar for my fact table, I add a flag for different events (days of current month, days of current year, year to date analysis...) so instead of checking the field YearMonth, you may check the newly created Last6Months, for example.

On the other hand, you may add to your set analysis something like

{< Last6Months = {1}, SalesAmount = {0} >}

Hope that helps

[#3]

Hi Miguel,

I do understand what you're saying, that will mean in my script i'll have to create a flag:

LOAD * INLINE [

YearMonth, Last6Months

201002, 1

201003, 1

201004, 1

201005, 1

201006, 1

201007, 1

];

Which means every month i'll have to change the values manually.

or if i create a variable - vLast6Months = ('201002','201003','201004','201005','201006','201007')

and use it in my chart expression: count({$<~YearMonth= {"$(vLast6Months)"}>} distinct MemberNo),

it will work but again i will have to adjust the variable after each month to have a constant 'last6months' trend,

is there anything like - count({$<~YearMonth= {"left(v(Today),6)-6"}>} distinct MemberNo)

that i could use so that i do not have to manually change the values,

please let me know if you so not understand.

Thanks

[Miguel_Angel_Baeyens]

Hello Richard,

I meant different ways of flagging, for example, using yeartodate() function so you always know in run time whether a date is included in the last six months, and everytime you reload the document these values will be recalculated again, so it won't be necessary to rescript anything.

Instead of using the YearMonth field I'd rather use the Date field, so you can easily test it.

Hope that helps.

[#5]

I've just found out, need to use the equivalant of the P and E functions that are used in Qlikview 9, because i need to see who is not buying.... I think this is a lost cause in 8.5:(

[Miguel_Angel_Baeyens · Accepted solution]

Richard,

You may use in your set analysis

{1 - < Last6Months = {1} >}

Meaning all clients but those who have bought in the last 6 months.

[#7]

Thanks Miguel,

You have just solved many problems here....

Appreciate it.