How to calculate %

rmuhammad — Fri, 01 Dec 2017 17:18:21 GMT

how do I calculate % by diving current month total with average of YTD? based on user selection for period

Re: How to calculate %

felipedl — Fri, 01 Dec 2017 19:23:10 GMT

Let`s say you have the data into Qlikview as the attached excel file and let`s say that with the following script:

Load

// easier to deal dates when they are numbers in set analysis

Num(Date) as NumDate;

LOAD Year,

Upper(Month) as Month,

// date

Date#(Year&'-'&Month,'YYYY-MMM') as Date,

Value

FROM

(ooxml, embedded labels, table is Plan1);

I load it up in qlikview.

The following expression would get what you need:

Current Month Value

sum({<NumDate={"$(=Num(MonthStart(Today())))"}>}Value)

YTD Value

sum({<NumDate={"<=$(=Num(Today()))"}>}Value)

Percentage

sum({<NumDate={"$(=Num(MonthStart(Today())))"}>}Value)

sum({<NumDate={"<=$(=Num(Today()))"}>}Value)

And gives the following:

See the attached QVW with the data and expression.

Felipe.