topic Re: Help With Set Analysis in QlikView

Help With Set Analysis

Sat, 07 Jul 2012 21:46:42 GMT

Hi everyone,

I am trying to calculate the number of Items where an Item with PCatID = 2 or 3 or 4 comes after PCatID =1. (please see the attached document)

I think I could use a set analysis expression that evaluates as:

count(ItemID) where max(creationdate) for PCatID=2,3 or 4 is greater than max(CreationDate) with PCatID=1.

How can I do this straight and simple?

[file attached]

Regards and Thanks,

-Khaled.

Re: Help With Set Analysis

swuehl — Sat, 07 Jul 2012 22:04:18 GMT

Try either

=count({<ItemID = {"=only({<PCatID={1}>}CreationDate) < max({<PCatID={2,3,4}>}CreationDate)"}>}distinct ItemID)

=count(aggr(if(only({<PCatID = {1}>}CreationDate)< max({<PCatID={2,3,4}>} CreationDate),ItemID), ItemID))

Re: Help With Set Analysis

Sun, 08 Jul 2012 13:15:55 GMT

Hello Stefan,

Thank you for your response.

Yes, did tweak the time format a little and it works perfectly fine.

Regards & Thanks,

-Khaled

Re: Help With Set Analysis

Wed, 18 Jul 2012 21:33:48 GMT

Dear Stefan,

Quite some time since the last message, but we have a slight change of requirement and I need help/suggestion.

In the same expression you mentioned, we now have three dimensions (RegionName, CityName, AreaName instead of ItemCode that we used previously) .

The modified expression gives the correct result when the dimensions are selected, but otherwise the count of ItemCode when PCatID=1 is greater than the count when PCatID =1 after 2. In short, the required count is greater than what is required with no filters applied, but gives a correct value with the dimension filters applied.

The expression used is more or less like this:

=count(aggr(if(only({<PCatID = {1}>}CreationDate)< max({<PCatID={2,3,4}>} CreationDate),RegionName,CityName,AreaName), ItemID))

Can you please suggest what could be wrong with the result or the expression?

Regards & Thanks,

-Khaled.

Re: Help With Set Analysis

swuehl — Thu, 19 Jul 2012 09:45:33 GMT

Looking at

=count(aggr(if(only({<PCatID = {1}>}CreationDate)< max({<PCatID={2,3,4}>} CreationDate),RegionName,CityName,AreaName), ItemID))

I think you want RegionName, CityName, AreaName to be aggr() dimensions, not part of the if() statement, right?

Doesn't this gives you an error in expression, because of too many arguments to if()?

So maybe try

=count(aggr(if(only({<PCatID = {1}>}CreationDate)< max({<PCatID={2,3,4}>} CreationDate),ItemID),RegionName,CityName,AreaName))

If this is not the problem, could you upload an updated sample file?

Re: Help With Set Analysis

Thu, 19 Jul 2012 11:35:26 GMT

Yes, the reqt. is to get the aggr. as you correctly mentioned...

Please find the sample data attatched for your reference.

Regards & Thanks,

-Khaled.

Re: Help With Set Analysis

swuehl — Thu, 19 Jul 2012 11:55:36 GMT

That's quite a big (in size) sample to demonstrate your issue, maybe too big for me to look into your data in detail.

Any chance to create a sample like your first one, just extended by the additional dimensions?

Besided the size, I can't see an issue with your data (probably because I just haven't fully understood it). Selecting one of the two document bookmarks haven't added some insight for me, too.

Could you maybe point me to the issue in more detail? What are you selecting, what are you seeing in which table and what do you expect to see?

Re: Help With Set Analysis

Tue, 24 Jul 2012 12:26:24 GMT

Dear Stefan,

It took me a little long, but I now have the same sample in English.

To be precise about the problem, the percentages do not seem to be evaluating correctly.

Please find the sample attached.

P.S: The file is quite big for a sample (as you rightly pointed out), but the problem is very barely noticeable when using smaller data samples.

Regards,

-Khaled.

Re: Help With Set Analysis

Mon, 30 Jul 2012 20:14:32 GMT

Dear,

Stefan.

Please Can You help us in the above scenario.

Thanks,

Mukram.

Re: Help With Set Analysis

swuehl — Mon, 30 Jul 2012 21:42:58 GMT

I admit I haven't fully understand your data (and the mere mass made it quite hard to look into).

I think you can try either

=Count(

(Aggr

(if

(Only({<ItemTransactions.ProcedureCatID = {1}>}ItemTransactions.CreationDate)

Max(

{<ItemTransactions.ProcedureCatID={2,3,4,5}>}ItemTransactions.CreationDate)

,ItemTransactions.ItemCode1),

ItemTransations_TransDirectorateFrom.RegionID,

ItemTransations_TransDirectorateFrom.CityEnName,

ItemTransations_TransDirectorateFrom.DirectorateEnDesc,

ItemTransactions.ItemCode1)))

=Count(

(Aggr

(if

(Only({<ItemTransactions.ProcedureCatID = {1}>}ItemTransactions.CreationDate)

Max(

total< ItemTransations_TransDirectorateFrom.RegionID,

ItemTransations_TransDirectorateFrom.CityEnName,

ItemTransations_TransDirectorateFrom.DirectorateEnDesc>

{<ItemTransactions.ProcedureCatID={2,3,4,5}>}ItemTransactions.CreationDate)

,ItemTransactions.ItemCode1),

ItemTransations_TransDirectorateFrom.RegionID,

ItemTransations_TransDirectorateFrom.CityEnName,

ItemTransations_TransDirectorateFrom.DirectorateEnDesc,

ItemTransactions.ItemCode1)))

Re: Help With Set Analysis

Tue, 31 Jul 2012 19:52:37 GMT

Dear,

Stepan.

Thanks for your help.

The above solution is working fine.

Thanks,

Mukram.

Re: Help With Set Analysis

Wed, 01 Aug 2012 07:15:37 GMT

Dear Stefan,

Thanks a lot for your help. It's fixed now.

Regards & Thanks,

-Khaled