Set analysis filter a dimension

[#1]

hi I need a bit of help. I am trying to analyse a large dataset to see what clients/customers are driving "losses" .... as such I should be able to select the "n" clients who have a profit <0

this is the set expression I wrote. it doesn't seem to work in Qliksense and I cant apply a filter too... any help would be appreciated. thank you

sum({$<CLIENT={"$(=aggr(sum(PROFIT),CLIENT)<0)"}>} PROFIT)

[Gysbert_Wassenaar]

Try this: sum({$<CLIENT={"=sum(PROFIT)<0"}>} PROFIT)

[marcus_sommer]

I assume that you have Client as dimension and then you didn't need the aggr, try it with something like this:

sum({$<CLIENT={"=sum(PROFIT)<0"}>} PROFIT)

or with aggr

sum({$<CLIENT={"=sum(aggr(sum(PROFIT),CLIENT))<0"}>} PROFIT)

and as listbox-expression

aggr(sum(PROFIT)<0, Client)

- Marcus

[#4]

THank you Marcus_Sommer and gwassenaar I got this now. I am still wondering I could have it as a "button" to click and select in one go all the clients that are not profitable. I think its possible in QV... I just don't know how to do it on sense.

THank you for your help.

neil@

[jagan · Accepted solution]

Hi,

In Qliksense you can achieve the same thing like this

Inline Table in script:

CustomerType:

LOAD

*

INLINE [

CustomerType

Profitable

Non-Profitable];

Now use the above dimension as filter, and in expression of the chart give like below

=If(GetFieldSelections(CustomerType) = 'Profitable', sum({$<CLIENT={"=sum(PROFIT)>0"}>} PROFIT),

If(GetFieldSelections(CustomerType) = 'Non-Profitable', sum({$<CLIENT={"=sum(PROFIT)<0"}>} PROFIT),

Sum(MeasureName)))

Hope this helps you.

Regards,

Jagan.

[#6]

Thank you Jagan. what does the "Sum(MeasureName)))" do at the end?

[jagan]

Sorry it is Sum(PROFIT).

Regards,

Jagan.

[#8]

Thank you Jagan.  Somehow I get exactly the same numbers for both Profitable and not profitable… not sure if its working.. ☹

[jonathandienst]

neil rodrigues wrote:

THank you Marcus_Sommer and gwassenaar I got this now. I am still wondering I could have it as a "button" to click and select in one go all the clients that are not profitable. I think its possible in QV... I just don't know how to do it on sense.

THank you for your help.

neil@

In both QV and QS, a selection can only be made of a dimension, or a field, not of a calculated value -- which exists only in the context of the sheet object containing the calculation. Selecting a value in an expression column is actually a selection on the dimensions for that row.

If you determine profitability in the load script and set a flag, then you can make the selection using the flag. You will need to do this at the appropriate level of granularity for your system.

[#10]

Thank you Jonathan. I did use the flag_feild concept (did help me to some extent)...

now I have been asked to do something more complicated... dynamically quarltile the customers depending on profit

I used this from

Aggr(Pick(Ceil(4*Rank(sum(PROFIT),CLIENT)/Count(distinct total CLIENT)),

        '1st quartile','2nd quartile','3rd quartile','Bottom quartile'), CLIENT)

I get the data in 4 quartiles but the client count is equally split into 4 buckets instead of a percentage based split (like 25% etc) how can I get this to show quartiles? Top 25%, Top50, ....#

thank you