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)
Transactions alst campaign
Last campaign period
40160 - 40170
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.
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.