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

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
swuehl
MVP
MVP

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

View solution in original post

11 Replies
swuehl
MVP
MVP

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

See also attached.

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
Author

Hello Stefan,

Thank you for your response.

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

Regards & Thanks,

-Khaled

Not applicable
Author

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.

swuehl
MVP
MVP

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
Author

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

Please find the sample data attatched for your reference.

Regards & Thanks,

-Khaled.

swuehl
MVP
MVP

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
Author

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.

Not applicable
Author

Dear,

Stefan.

Please Can You help us in the above scenario.

Thanks,

Mukram.

swuehl
MVP
MVP

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