How to calculate %

[rmuhammad]

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

[felipedl · Accepted solution]

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

x:

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.