Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rmuhammad
Creator
Creator

How to calculate %

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

annual.PNG

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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:

Sample.png

See the attached QVW with the data and expression.

Felipe.

View solution in original post

1 Reply
felipedl
Partner - Specialist III
Partner - Specialist III

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:

Sample.png

See the attached QVW with the data and expression.

Felipe.