Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

Tags (4)
1 Solution

Accepted Solutions
MVP
MVP

Re: Whats wrong with this expression?

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

8 Replies
Not applicable

Re: Whats wrong with this expression?

Try something like: -

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

Not applicable

Re: Whats wrong with this expression?

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

Re: Whats wrong with this expression?

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

Re: Whats wrong with this expression?

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

MVP
MVP

Re: Whats wrong with this expression?

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

Re: Whats wrong with this expression?

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

Swuehl: Whats was the logic behind that?

MVP
MVP

Re: Whats wrong with this expression?

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

Re: Whats wrong with this expression?

Well explained. Will remember that. Thank you.

Community Browser