Set Analysis counting value instances from prev month to this month

[#1]

Hi,

New  to Qlikview and after help for set analysis.

I has data set like below;

Date                CustID
1/08/2014          1

1/08/2014          2

1/08/2014          3

1/08/2014          4

1/08/2014          5

1/09/2014          2

1/09/2014          3

1/09/2014          6

1/09/2014          7

1/09/2014          8

Id like to count where custID is same from Aug to Sep (i.e. 2) how many custID repeates there are.  So result from above would equal 2 (i.e. 2 and 3 appear in oth months.  I have 2 variables covering dates  $(vThisMonth)=1/09/2014 and $(vLastMonth)=1/08/2014.

Thanks in advance,

Jason

[kogasawara]

try this

=count({<Date={"$(vThisMonth)"},CustID=P({<Date={"$(vLastMonth)"}>}CustID)>}CustID)

[#3]

Thanks kogasawara.

I'm getting a result of 0.  Should be getting a result of 16!!!  What am I doing wrong!!!???

Apologies, the above description was sample only.  The data I have I can't really share.  Howevver data fomat exactly the same.  Names have just been changed to protect the innocent 🙂

However I could add field names an all still ok, see below;

count({<Date={"$(vThisMonth)"},NULL_MSISDN=P({<Date={"$(vLastMonth)"}>}NULL_MSISDN)>}NULL_MSISDN)

[kogasawara]

check the attached qvw file.

in your sample case,you can count only 2 and 3 CustID.

[jonathandienst]

Hi

Not sure about your date formats, if they are all the same, then:

=count({<Date={">=$(=vThisMonth)"}> * <Date={"<=$(=vLastMonth)"}>} DISTINCT CustID)

If they need to be explicitly formatted:

=count({<Date={">=$(=Date(Date#(vThisMonth), 'D/M/YYYY'))"}> * <Date={"<=$(=Date(Date#(vLastMonth), 'D/M/YYYY'))"}>} DISTINCT CustID)

HTH

Jonathan

[jonathandienst]

On testing, my previous post did not work, but try this:

=Count({<CustID = P({<Date={"$(=vThisMonth)"}>} CustID)> * <CustID=P({<Date={"$(=vLastMonth)"}>} CustID)>} DISTINCT CustID)

HTH

Jonathan

[jagan]

Hi,

Try like this

LOAD

*,

Num(MonthStart(Date#(Date, 'D/MM/YYYY'))) & '_' & CustID AS Key

INLINE [

Date,                CustID

1/08/2014,          1

1/08/2014,          2

1/08/2014,          3

1/08/2014,          4

1/08/2014,          5

1/09/2014,          2

1/09/2014,3

1/09/2014,          6

1/09/2014,          7

1/09/2014,          8 ];

Dimension: CustID

Expression: If(Count({<Date={">=$(=vLastMonth)<=$(=vThisMonth)"}>} DISTINCT Key) > 1, Count({<Date={">=$(=vLastMonth)<=$(=vThisMonth)"}>} DISTINCT Key))

Hope this helps you.

Regards,

Jagan.

[#8 · Accepted solution]

Thanks Jonathon.

Bingo.  Got there (of sorts)

If I manually added dates it worked.  For some reson variables weren't recognised.  However, if autogen load the variables they are being presented in correct format.

So wohat worked was below;

=count({<Date={"01/09/2014"},NULL_MSISDN=P({<Date={"01/08/2014"}>}NULL_MSISDN)>}NULL_MSISDN)

This will do for now.  Will explore tomorrow.  Thanks to you and others whoi replied.

[#9]

Something to ponder for tomorrow and all wlecome to take a crack;

Why does the below work like a dream;

=count({<Date={"01/09/2014"},NULL_MSISDN=P({<Date={"01/08/2014"}>}NULL_MSISDN)>}NULL_MSISDN)

However the below fails;

=count({<Date={"=$(=vThisMonth"},NULL_MSISDN=P({<Date={"=$(=vLastMonth"}>}NULL_MSISDN)>}NULL_MSISDN)

[anbu1984]

You mentioned variables in your post without 0

$(vThisMonth)=1/09/2014 and $(vLastMonth)=1/08/2014.

From your working expr, it seems you have dates with 0 in months

Why does the below work like a dream;

=count({<Date={"01/09/2014"},NULL_MSISDN=P({<Date={"01/08/2014"}>}NULL_MSISDN)>}NULL_MSISDN)

If format of Date field and variable vLastMonth and vLastMonth matches, then your below expr should work

=count({<Date={"=$(=vThisMonth"},NULL_MSISDN=P({<Date={"=$(=vLastMonth"}>}NULL_MSISDN)>}NULL_MSISDN)