topic Re: Complex Set Analysis Question Please.... in QlikView

Complex Set Analysis Question Please....

Anonymous — Thu, 29 Jun 2017 14:30:29 GMT

Hi there,

I am really struggling with a set analysis requirement and any feedback would be much appreciated!

I have a filter pane in Qlik Sense named 'ISBN'

I am currently using the following set analysis expression to return ISBNs with the same possible values attributed to a selected ISBN from the filter pane.

Sum({$< ISBN = {*},Genre = p(Genre), [Binding Desc] = p([Binding Desc]),[Series Flag] = p([Series Flag]) >}[Total Sales])

The requirement is to be able to select multiple ISBN's, and return 10 similar ISBNs to each ISBN selected with the same possible values attributed to the ISBN.

The calculation works when selecting one ISBN and limiting the dimension to getselectedcount(ISBN)*10

When I select two, I have 20 results, but based on the possible values of both selected ISBN's combined, not each separately.

Any help would be greatly received.

Thanks,

Carmelle

Re: Complex Set Analysis Question Please....

sunny_talwar — Thu, 29 Jun 2017 14:47:10 GMT

Would you be able to share a sample?

Re: Complex Set Analysis Question Please....

Anonymous — Thu, 29 Jun 2017 15:22:45 GMT

Thanks,

I have attached a sample application.

Any help at all would be much appreciated!

Re: Complex Set Analysis Question Please....

sunny_talwar — Thu, 29 Jun 2017 15:26:17 GMT

Unable to open the file you have attached

Re: Complex Set Analysis Question Please....

Anonymous — Thu, 29 Jun 2017 15:31:49 GMT

Can you open this application?

Thanks

Re: Complex Set Analysis Question Please....

sunny_talwar — Thu, 29 Jun 2017 15:33:29 GMT

Nope, what you are attaching doesn't even have .qvw extension. Not even sure what these files are. I tried to add .qvw at the end and open them, but it won't open

Re: Complex Set Analysis Question Please....

Anonymous — Thu, 29 Jun 2017 15:35:54 GMT

Apologies, this should work

Thanks

Re: Complex Set Analysis Question Please....

sunny_talwar — Thu, 29 Jun 2017 16:19:08 GMT

So here is the thing... I have it working when there is only Binding Desc difference between the two ISBN you select.... Individually I can fix all of them. But to make this more dynamic, create a field in the script like this

Data_Temp:

LOAD

ISBN,

LanguageCD,

"Author",

Publisher,

"Pub Date",

"Binding Desc",

"Series Flag",

Sales as [Total Sales],

Literal,

Literal1 AS Genre,

Literal1,

Literal2,

Literal3,

Literal4,

AutoNumber(Literal1&Literal2&Literal3&"Binding Desc"&"Series Flag") as Key

FROM [lib://Data Source (qlik-sense_administrator)/data_temp.qvd]

(qvd);

And then use this expression

=If(Only({1}Aggr(Rank(

If(Sum({$<ISBN = {*},Literal1 = ($(vBISAC1_Match)),[Series Flag] = ($(vSeriesFlag_Match)), [Binding Desc] = ($(vBinding_Match)) >}[Total Sales]) > 0,

Only({$<ISBN = {*},Literal1 = ($(vBISAC1_Match)),[Series Flag] = ($(vSeriesFlag_Match)), [Binding Desc] = ($(vBinding_Match))>} ISBN))), Key, ISBN)) <11,

Sum({$<ISBN = {*},Literal1 = ($(vBISAC1_Match)),[Series Flag] = ($(vSeriesFlag_Match)), [Binding Desc] = ($(vBinding_Match)) >}[Total Sales]))

And make sure to remove the dimension limits from the ISBN dimension.

Re: Complex Set Analysis Question Please....

Anonymous — Fri, 30 Jun 2017 08:10:07 GMT

Thank you very much for this! Exactly what I needed.

Re: Complex Set Analysis Question Please....

sunny_talwar — Fri, 30 Jun 2017 11:18:51 GMT

Awesome