Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
carm1988
Contributor III
Contributor III

Complex Set Analysis Question Please....

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

1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

9 Replies
sunny_talwar

Would you be able to share a sample?

carm1988
Contributor III
Contributor III
Author

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

carm1988
Contributor III
Contributor III
Author

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

carm1988
Contributor III
Contributor III
Author

Apologies, this should work

Thanks

sunny_talwar

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.

carm1988
Contributor III
Contributor III
Author

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

sunny_talwar

Awesome