count of unique values inside the period - Qlik Community

ziabobaz · ‎2017-05-22

Hello,

I have a list of transaction per day, split by customers. One customer can buy more than one time within a time period.

I want to count unique transaction.

When i choose a MONTH_YEAR in a filter, my formula gives me correct number of transaction:

sum( AGGR ( count(Client) ,Date ) ) = 6.

When I choose DATE as a filter (for example 01 and 02 of April) , it returns the wrong result.

I tried to reverse the formula: sum( AGGR ( count(Date) ,Client ) ) , and it works fine both with MONTH_YEAR and DATE filters, but when I build a PIVOT with Client as ROW and MONTH_YEAR as column, it won't work (it works only with the simple formula count (client) )

What's wrong?

My data as an example:

lironbaram · ‎2017-05-22

hi

if you want to count unique transaction , if i understand correctly you want to count all the rows in the table

so why can't you use count(client) for all levels of displays

ziabobaz · ‎2017-05-22

it works if my dimension is DATE

it does not work without dimension

thus, if i use KPI which shows number of purchases for whenever period, i have to explicitly state that it should first aggregate the purchases by date and then sum them up.

It works fine until i want to use this KPI as measure with MONTHS as dimension, and i don't realize why.

Anonymous · ‎2017-05-22

I think Liron is asking - why can't use just use =count(distinct Client) without aggr.

Or even something like =count(distinct Client & Date)

ziabobaz · ‎2017-05-22

because unless I use DATE as dimension, count(client) will merge all purchase occurrences from a single client in one single purchase.

count (date) will work only if the dimension is CLIENT. If not, it will return number of days in a given period.

Anonymous · ‎2017-05-22

I understand what you're saying now. It would be helpful if you had a sample app to share?

ziabobaz · ‎2017-05-26

Unfortunately it is not possible - I'd have to spend hours to create a small sample of my original data.

I think I would be using the sum( AGGR ( count(Client) ,Date ) ) as it works for me, at least for now.

Thank you!