topic Re: Whats wrong with this expression? in QlikView

Whats wrong with this expression?

Tue, 21 Aug 2012 10:49:12 GMT

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.

Re: Whats wrong with this expression?

Tue, 21 Aug 2012 10:59:46 GMT

Try something like: -

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

Re: Whats wrong with this expression?

Tue, 21 Aug 2012 11:01:53 GMT

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

Re: Whats wrong with this expression?

Tue, 21 Aug 2012 11:04:50 GMT

Felim Shanaghy:

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

Any other ideas?

Re: Whats wrong with this expression?

Tue, 21 Aug 2012 11:07:21 GMT

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

Re: Whats wrong with this expression?

swuehl — Tue, 21 Aug 2012 11:30:49 GMT

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

Re: Whats wrong with this expression?

Tue, 21 Aug 2012 11:33:39 GMT

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

Swuehl: Whats was the logic behind that?

Re: Whats wrong with this expression?

swuehl — Tue, 21 Aug 2012 11:48:57 GMT

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

Re: Whats wrong with this expression?

Tue, 21 Aug 2012 12:01:48 GMT

Well explained. Will remember that. Thank you.