topic Re: Can a set expression be filtered, before and after calculation? in App Development

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

Anonymous — Thu, 19 Jul 2018 23:36:58 GMT

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)

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

petter — Fri, 20 Jul 2018 00:04:56 GMT

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

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

Anonymous — Fri, 20 Jul 2018 00:08:51 GMT

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.

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

sunny_talwar — Fri, 20 Jul 2018 02:16:19 GMT

Are you may be looking for this

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

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

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

parimikittu — Fri, 20 Jul 2018 05:59:51 GMT

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.

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

Anonymous — Mon, 23 Jul 2018 20:47:12 GMT

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.