Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello.
I have a dummy table like this:
ProductCode | BeginDate | EndDate |
---|---|---|
Product 1 | 40100 | 40110 |
Product 2 | 40120 | 40130 |
Product 3 | 40140 | 40150 |
Product 1 | 40160 | 40170 |
Product 2 | 40170 | 40180 |
Product 3 | 40190 | 40200 |
ProductCode | TransDate | TransId |
---|---|---|
Product 1 | 40101 | random # |
Product 1 | 40161 | random # |
Product 2 | 40121 | random # |
Product 2 | 40171 | random # |
Product 3 | 40141 | random # |
Product 3 | 40191 | random # |
I am trying to use a straight table to count the transaction ID-s (TransId) of products in the last campaign by comparing the sales date (TransDate) with the highest BeginDate to the highest EndDate (Only the rows marked as bold are the ones i want to count)
ProductCode | Transactions alst campaign | Last campaign period |
---|---|---|
Product 1 | 1 | 40160 - 40170 |
Product 2 | 1 | 40170-40180 |
Product 3 | 1 | 40190-40200 |
Min() and max() dont work inside if() condition so i used aggr.
=count(distinct if(TransDate>=aggr(max(BeginDate),ProductCode) AND TransDate<=aggr(max(EndDate),ProductCode),TransId))
But it does not work, i get 0 or 1, when there should be 10 or 100. (in real data)
When i use =aggr(max(BeginDate),ProductCode) in the next column or replace the aggr() in the formula with date numbers i get the correct result.
What's wrong with my code?
I also added dummy sales table where are the IDs i want to count and how the result table should look like.
Try a NODISTINCT qualifier with your aggr() functions:
=concat(distinct if(TransDate>=aggr(nodistinct max(BeginDate),ProductCode) AND TransDate<=aggr(nodistinct max(EndDate),ProductCode),TransId))
Try something like: -
Count(Distinct {$<TransDate={">=$(=aggr(max(BeginDate),ProductCode))<=$(=aggr(max(EndDate),ProductCode))"}>} TransId)
Hi,
With the dummy table you have attached it will give you one as none of the date for any ProductId coincide i believe what you want is:
=count(distinct if(TransDate>=aggr(min(BeginDate),ProductCode) AND TransDate<=aggr(max(EndDate),ProductCode),TransId))
Regards
RL
Felim Shanaghy:
Now i get all 0-s.
When i replace the aggr wth dates i get the correct answer...
Any other ideas?
lakhina that makes no difference either and i do want max BeginDate and max EndDate because that was the last campaign for that product.
Try a NODISTINCT qualifier with your aggr() functions:
=concat(distinct if(TransDate>=aggr(nodistinct max(BeginDate),ProductCode) AND TransDate<=aggr(nodistinct max(EndDate),ProductCode),TransId))
That looks more like the numbers i would like to see.
Swuehl: Whats was the logic behind that?
Yes, I think that sounds complicated and is probably one of the least intuitive features in QV.
Your aggr() function will bey default return only one value per combination of dimension values, but I think you are calling the aggr() function multiple times, once per line in your transaction table. So you need to specify the nodistinct qualifier to allow QV to repeat the aggr() values multiple times.
Not sure if this clarifies anything...
Well explained. Will remember that. Thank you.