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

Whats wrong with this expression?

Hello.

I have a dummy table like this:

ProductCodeBeginDateEndDate
Product 14010040110
Product 24012040130
Product 34014040150
Product 14016040170
Product 24017040180
Product 34019040200



ProductCodeTransDateTransId
Product 140101random #
Product 140161random #
Product 240121random #
Product 240171random #
Product 340141random #
Product 340191random #

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)

ProductCodeTransactions alst campaignLast campaign period

Product 1

140160 - 40170
Product 2140170-40180
Product 3140190-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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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))

View solution in original post

8 Replies
Not applicable
Author

Try something like: -

Count(Distinct {$<TransDate={">=$(=aggr(max(BeginDate),ProductCode))<=$(=aggr(max(EndDate),ProductCode))"}>} TransId)

Not applicable
Author

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

Not applicable
Author

Felim Shanaghy:

Now i get all 0-s.
When i replace the aggr wth dates i get the correct answer...

Any other ideas?

Not applicable
Author

lakhina that makes no difference either and i do want max BeginDate and max EndDate because that was the last campaign for that product.

swuehl
MVP
MVP

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))

Not applicable
Author

That looks more like the numbers i would like to see.

Swuehl: Whats was the logic behind that?

swuehl
MVP
MVP

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...

Not applicable
Author

Well explained. Will remember that. Thank you.