Can a set expression be filtered, before and after calculation?

[Anonymous]

Let's say I have a set expression to calculate Sales Percent Share by month, by year:

Sum(Sales)/Sum(Total <Year, Month> Units)

I want to calculate the percentage for my Top 5 Accounts - i.e. filter before the calculation, this is the default way a filter behaves.

But in addition to that, I only want to show one Account at a time, without the percentages recalculating. If I filter to one Account right now, all the percentages go to 100%, because the data is being filtered pre-set expression calculation.

I'm more familiar with Tableau, and it's easy to do this using the LOOKUP function, to force the filter after the set expression (in Tableau you would call it a table calculation)

[petter]

Inter-record functions in Qlik can give you something similar to table calculations.

[Anonymous]

Which function would you suggest using, and do you have to do it in the data load phase? `LookUp` for instance, can't be called in the design views.

[sunny_talwar]

Are you may be looking for this

Sum({<Account = {"=Rank(Sum(Sales)) < 6"}>}Sales)/Sum(Total <Year, Month> {<Account>} Units)

or

Sum({<Account = {"=Rank(Sum(Sales)) < 6"}>}Sales)/Sum(Total <Year, Month> {<Account = {"=Rank(Sum({1} Sales)) < 6"}>} Units)

[parimikittu]

Sum(Sales)/Sum(Total Units), In this formula, your numerator and denominator will change per selections. If you don't have to change the denominator part use below formula Sum({1} Total Units), {1} will give u the total irrespective of selections.

[Anonymous]

Hi Vijay,

{1} doesn't give me what I'm looking for, because I'm looking for a subset of a subset. Using SUM({1} TOTAL Units) in the denominator gives you percent of the grand total, and I'm looking for percent of 5 selected accounts.