topic Re: how? calculating % by dividing total for year by avg of totals from past months same fy year in QlikView

how? calculating % by dividing total for year by avg of totals from past months same fy year

rmuhammad — Wed, 25 Nov 2020 16:16:04 GMT

how do i achieve this? I need to calculate % by dividing total for year by avg of totals from past months same fy year

sample data file attached

Re: how? calculating % by dividing total for year by avg of totals from past months same fy year

felipedl — Tue, 28 Nov 2017 19:57:36 GMT

Hi Rashid,

Both can be achieved through set analysis, being:

Column B (gets the max period and snapperiod or selected by user):

sum({<period={"$(=max(period))"},snapperiod={"$(=max(snapperiod))"}>}rate)

Column C (gets the year interval to max snapperiod or selected snapperiod:

avg({<snapperiod={">=$(=Yearstart(max(snapperiod)))<=$(=max(snapperiod))"}>}rate)

and Column 😧

// It only gets the previous stated expressions and does the percentage

Column(1)/Column(2)

Attachedt he file you provided with teh "New Graph" table, wich holds the above expression.

Felipe.

Re: how? calculating % by dividing total for year by avg of totals from past months same fy year

rmuhammad — Wed, 29 Nov 2017 18:53:52 GMT

this works when columns are calculated separately, what if I have one expression in the pivot table and different partial sum sort of like

now in this I want to divide Status total /Year Total

Re: how? calculating % by dividing total for year by avg of totals from past months same fy year

felipedl — Thu, 30 Nov 2017 12:00:31 GMT

I guess you're missing the point of set analysis, if you combine both expression into one, it gets the same result, I just assumed you needed to see both values independently, but you can get only one expression with the two I put on the graph, giving the following:

Set analysis let's you get a specific selection of data, even though there's a different selection made by the user.

Felipel.