topic Re: New Buyers to a Branch in QlikView

New Buyers to a Branch

Anonymous — Tue, 07 Jul 2015 13:00:56 GMT

Hi all I am wanting to count the number of new buyers to a specific branch.

New buyers would be a buyer who has bought a unit in June but not between Dec - May at that same branch.

=aggr(if(

count(

{< SOLD.YrMthSeq= {"<=$(vRptYrMthSeqTY ) >=$(vRptYrMthSeqTY) "},

UNIT.STOCKSTATUS={'Sold'} >}

UNIT.TOTALSOLD) > 0

and

count(

{<SOLD.YrMthSeq= {"<=$(=$(vRptYrMthSeqTY) -1) >=$(=$(vRptYrMthSeqTY) -6)"},

UNIT.STOCKSTATUS={'Sold'} >}

UNIT.TOTALSOLD) = 0 ,

count( BUYER.B_NO)) , BUYER.B_NO)

My dimension is branch and the first set analysis is asking if they have bought something in june the second is if they have bought something between Dec - May.

Any help would be very appreciated.

Thanks

Gareth

Re: New Buyers to a Branch

sinanozdemir — Tue, 07 Jul 2015 13:18:03 GMT

Hi Gareth,

I believe you also want to aggregate by Branch name or no as well. If that doesn't work, would you mind posting some sample data?

Thanks

Re: New Buyers to a Branch

sunny_talwar — Tue, 07 Jul 2015 13:19:09 GMT

I would first check if these give you correct values when you use them in a straight table with BUYER.B_NO as Dimension:

Count({< SOLD.YrMthSeq= {"<=$(vRptYrMthSeqTY ) >=$(vRptYrMthSeqTY) "},UNIT.STOCKSTATUS={'Sold'} >} UNIT.TOTALSOLD)

Count({<SOLD.YrMthSeq= {"<=$(=$(vRptYrMthSeqTY) -1) >=$(=$(vRptYrMthSeqTY) -6)"}, UNIT.STOCKSTATUS={'Sold'} >} UNIT.TOTALSOLD)

If those are correct then we can make sure that we are moving in the right direction, but if not I would focus my attention of fixing them first.

HTH

Best,

Sunny

Re: New Buyers to a Branch

crusader_ — Tue, 07 Jul 2015 13:24:28 GMT

Hello Gareth,

Try to use sum(if ()) construction to achive require result.

sum(

if(

count(

{< SOLD.YrMthSeq= {"<=$(vRptYrMthSeqTY ) >=$(vRptYrMthSeqTY) "},

UNIT.STOCKSTATUS={'Sold'} >}

UNIT.TOTALSOLD) > 0

and

count(

{<SOLD.YrMthSeq= {"<=$(=$(vRptYrMthSeqTY) -1) >=$(=$(vRptYrMthSeqTY) -6)"},

UNIT.STOCKSTATUS={'Sold'} >}

UNIT.TOTALSOLD) = 0 ,

1, 0)

)

Hope this helps you.

Regards,

Andrei

Re: New Buyers to a Branch

Anonymous — Tue, 07 Jul 2015 13:29:52 GMT

Hi Sunny,

Originally I had =if([June Buyers] >0 and [May - Dec Buyers] = 0 , 1) as the expression

with [June Buyers] being Count({< SOLD.YrMthSeq= {"<=$(vRptYrMthSeqTY ) >=$(vRptYrMthSeqTY) "},UNIT.STOCKSTATUS={'Sold'} >} UNIT.TOTALSOLD)

and

[May - Dec Buyers] being Count({<SOLD.YrMthSeq= {"<=$(=$(vRptYrMthSeqTY) -1) >=$(=$(vRptYrMthSeqTY) -6)"}, UNIT.STOCKSTATUS={'Sold'} >} UNIT.TOTALSOLD)

this worked and flagged the buyers that fall into this criteria.

But I need this to be totalled up for each Branch.

Thanks

Gareth

Re: New Buyers to a Branch

Anonymous — Tue, 07 Jul 2015 13:32:11 GMT

Thanks Andrei but that is just showing null values?

Re: New Buyers to a Branch

crusader_ — Tue, 07 Jul 2015 13:44:52 GMT

Hm... very strange..

Does it work if you put your original flag into sum()- function?

Gareth Cox-Thorpe wrote:

Originally I had =if([June Buyers] >0 and [May - Dec Buyers] = 0 , 1) as the expression

Andrei

Re: New Buyers to a Branch

jonathandienst — Tue, 07 Jul 2015 13:46:25 GMT

I think this is closer to what you need. Change the Branch field to the correct field name.

=Sum(Aggr(

Count(Aggr(

If(Count({<SOLD.YrMthSeq={"<=$(vRptYrMthSeqTY) >=$(vRptYrMthSeqTY)"}, UNIT.STOCKSTATUS={'Sold'}>} UNIT.TOTALSOLD) > 0

And Count({<SOLD.YrMthSeq={"<=$(=$(vRptYrMthSeqTY)-1)>=$(=$(vRptYrMthSeqTY)-6)"}, UNIT.STOCKSTATUS={'Sold'} >} UNIT.TOTALSOLD) = 0,

DISTINCT BUYER.B_NO)

, BUYER.B_NO, Branch))

,Branch))

Re: New Buyers to a Branch

jonathandienst — Tue, 07 Jul 2015 13:48:19 GMT

Andrei

That expression does not work as you are trying to nest a Count() inside a Sum(). You need an Aggr() for the inner aggregation.

Re: New Buyers to a Branch

crusader_ — Tue, 07 Jul 2015 13:51:06 GMT

Oh.. yes.. indeed 🙂

Thanks Jonathan!

Re: New Buyers to a Branch

Anonymous — Tue, 07 Jul 2015 13:52:23 GMT

I think this is really close Jonathan however the expression doesn't like the DISTINCT part if I take it out it works but it wont be giving me an accurate number.

Re: New Buyers to a Branch

jonathandienst — Tue, 07 Jul 2015 13:54:59 GMT

My bad - the Distinct should be before the inner Aggr():

=Sum(Aggr(

Count(DISTINCT Aggr(

If(Count({<SOLD.YrMthSeq={"<=$(vRptYrMthSeqTY) >=$(vRptYrMthSeqTY)"}, UNIT.STOCKSTATUS={'Sold'}>} UNIT.TOTALSOLD) > 0

And Count({<SOLD.YrMthSeq={"<=$(=$(vRptYrMthSeqTY)-1)>=$(=$(vRptYrMthSeqTY)-6)"}, UNIT.STOCKSTATUS={'Sold'} >} UNIT.TOTALSOLD) = 0,

BUYER.B_NO)

, BUYER.B_NO, Branch))

,Branch))

Re: New Buyers to a Branch

Anonymous — Tue, 07 Jul 2015 13:58:16 GMT

Thank you so much Jonathan! That worked!!

Thanks

Gareth