New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Not applicable

Help With Set Analysis

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.

1 Solution

Accepted Solutions
MVP

Re: Help With Set Analysis

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

or

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

11 Replies
MVP

Re: Help With Set Analysis

Try either

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

or

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

You need to take care that your timestamps are correctly interpreted as such (using e.g. an appropriate standard timestamp format).

Regards,

Stefan

Not applicable

Re: Help With Set Analysis

Hello Stefan,

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

Regards & Thanks,

-Khaled

Not applicable

Re: Help With Set Analysis

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.

MVP

Re: Help With Set Analysis

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?

Not applicable

Re: Help With Set Analysis

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

Regards & Thanks,

-Khaled.

MVP

Re: Help With Set Analysis

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?

Not applicable

Re: Help With Set Analysis

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.

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.

Not applicable

Re: Help With Set Analysis

Dear,

Stefan.

Please Can You help us in the above scenario.

Thanks,

Mukram.

MVP

Re: Help With Set Analysis

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

or

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