Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to count the ids whose sum is greater than 3. let me explain with an example. Assume i have below table.
manager | wholeseller | supervisior | Id | date | Transaction Amt |
sagar | rahul | ram | 1 | 1/20/2016 | 1 |
sagar | rahul | ram | 1 | 1/7/2016 | 2 |
sagar | rahul | ram | 1 | 1/8/2017 | 5 |
sagar | rahul | ram | 2 | 9/5/2016 | 8 |
sagar | rahul | ram | 3 | 9/5/2018 | 7 |
Anindya | vikas | Deb | 10 | 4/8/2016 | 4 |
Anindya | vikas | Firoz | 4 | 5/4/2016 | 9 |
under the manager there are some wholeseller and under the wholersellers there are some supervisiors and further under the supervisior there are some ids those ids have some transaction on particular dates with some transaction amt. i need to find out the count of ids whose transaction amt is greater than equal to 3 in lets suppose in year 2016. My final table will look like after considering the condition
manager | wholeseller | supervisior | Count |
Sagar | Rahul | ram | 2(as id 1 has sum=3,id 2 has sum = 8, 3 has 0 sale in year=2016) |
Anindya | vikas | Deb | 1(id 10 has sum =4) |
Anindya | vikas | Firoz | 1(id 4 has sum = 9) |
Try this
=Count(DISTINCT {<Id = {"=Sum({<year = {2016}>}[Transaction Amt]) >= 3"}, year = {2016}>} Id)
Thank you sunny, However in case of same ids present under different supervisiors , is giving problem, for example for below table if i use Transaction Amt >= 6 without using date dimension for now
manager | wholeseller | supervisior | Id | date | Transaction Amt |
sagar | rahul | ram | 1 | 1/20/2016 | 1 |
sagar | rahul | ram | 1 | 1/7/2016 | 2 |
sagar | rahul | ram | 1 | 1/8/2017 | 5 |
sagar | rahul | ram | 2 | 9/5/2016 | 8 |
sagar | rahul | ram | 3 | 9/5/2018 | 7 |
Anindya | vikas | Deb | 2 | 4/8/2016 | 4 |
Anindya | vikas | Firoz | 4 | 5/4/2016 | 9 |
it will give me below output after using Transaction Amt >= 6 without considering the date for now.
manager | wholeseller | supervisior | Count |
Sagar | Rahul | ram | 2(as id 1 has sum=8,id 2 has sum = 8, 3 has 7 over sale) |
Anindya | vikas | Deb | 1(id 10 has sum =4 but still giving me count) |
Anindya | vikas | Firoz | 1(id 4 has sum = 9) |
Supervisior deb is still giving me a count as it is considering the id 1 although it's sum is 4 which is less than 6
May be you need this then
=Count({<year = {2016}>}DISTINCT Aggr(If(Sum({<year = {2016}>}[Transaction Amt]) >= 6, Id), Id, supervisior))