QlikView App Dev

[garethct]

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

[sinanozdemir]

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

[sunny_talwar]

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

[crusader_]

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

[garethct]

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

[garethct]

Thanks Andrei but that is just showing null values?

[crusader_]

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

[jonathandienst]

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))

[jonathandienst]

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.

[crusader_]

Oh.. yes.. indeed 🙂

Thanks Jonathan!