Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ziabobaz
Creator III
Creator III

count of unique values inside the period

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:

Screenshot_57.jpg

6 Replies
lironbaram
Partner - Master III
Partner - Master III

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
Creator III
Creator III
Author

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
Not applicable

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
Creator III
Creator III
Author

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
Not applicable

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

ziabobaz
Creator III
Creator III
Author

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!