Complex Set Analysis Question Please....

[Anonymous]

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

[sunny_talwar]

Would you be able to share a sample?

[Anonymous]

Thanks,

I have attached a sample application.

Any help at all would be much appreciated!

[sunny_talwar]

Unable to open the file you have attached

[Anonymous]

Can you open this application?

Thanks

[sunny_talwar]

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

[Anonymous]

Apologies, this should work

Thanks

[sunny_talwar · Accepted solution]

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.

[Anonymous]

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

[sunny_talwar]

Awesome