Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
try this
=count({<Date={"$(vThisMonth)"},CustID=P({<Date={"$(vLastMonth)"}>}CustID)>}CustID)
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)
check the attached qvw file.
in your sample case,you can count only 2 and 3 CustID.
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
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
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.
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.
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)
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)