Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
how do I calculate % by diving current month total with average of YTD? based on user selection for period
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.
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.