Count no of transactions within a value range

[#1]

Hi

I’m new to Qlik and am trying to  count the number of transactions where the total transaction amount is with a value range eg < 1000.

Eg

Transaction         Item      Amount

abc                          1                400

abc                          2              1200

abc                         3                400

def                          1                100

def                        2                200 

Expected  Result

Tansaction     Count(< 1000)

abc                           0

def                             1

I’ve tried the following expressions

  count({$<[Amount]={'>0<=1000'}>} Distinct Transaction)//count(DISTINCT [Transaction])

  this is not working as it evaluates each item of the transaction>

  eg it returns the result "1" for transaction abc  as the items 1 and 3 are  less than 1000.

This expression returns total of the  whole document

sum(DISTINCT TOTAL < [Transaction]> [Amount] )

ie  abc = 2000 and def = 300

I’ve tried to incorporate the sum expression into the count expression but haven’t been successful.

’ve also tried various if statement without success.

Any suggestions would be appreciated.

Thanks

[gerald_lebret]

Hello,

I know this post is a bit old but I am facing a similar issue.

I need to count the number of document which have an average price within a range of revenue.

I wanted to use the following formula but it does not work:

Count (distinct{<   
                           STAT_VENTE.DATE_DOCUMENT={'>16.03.2020'},
                           TYPE_DATA={"Ventes"},
                           STAT_VENTE.TYPE_CLIENT-={"employé","Famille"},
                           TYPE_GABARIT-={"TCPOS Ticket de caisse vente (TTC) - Fidel","TCPOS Ticket de caisse vente (TTC) - Passage"},
                           STAT_VENTE.DOC_ID ={" = sum(STAT_VENTE.MNT_NET_HT)<50"}
                           >}                                                   STAT_VENTE.DOC_ID)

The only solution I have found is the following:

Count(aggr(If(sum({< STAT_VENTE.DATE_DOCUMENT={'>16.03.2020'},
                                TYPE_DATA={"Ventes"},
                                STAT_VENTE.TYPE_CLIENT-={"employé","Famille"},
                                TYPE_GABARIT-={"TCPOS Ticket de caisse vente (TTC) - Fidel","TCPOS Ticket de caisse vente (TTC) - Passage"}
                      >}                                       STAT_VENTE.MNT_NET_HT)>0
              and sum({<   STAT_VENTE.DATE_DOCUMENT={'>16.03.2020'},
                                TYPE_DATA={"Ventes"},
                                STAT_VENTE.TYPE_CLIENT-={"employé","Famille"},
                                TYPE_GABARIT-={"TCPOS Ticket de caisse vente (TTC) - Fidel","TCPOS Ticket de caisse vente (TTC) - Passage"}
                      >}                                       STAT_VENTE.MNT_NET_HT)<=50
              and STAT_VENTE.DATE_DOCUMENT >'16.03.2020',STAT_VENTE.DOC_ID),STAT_VENTE.DOC_ID))

However, this formula requires too many resources and I need to use it 3 times but it keeps crashing after the second one.

Could you please help me out?

Thank you very much

Regards

Gerald