Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

juriengroot
New Contributor III

Use calculated value into new calculation

To create a forecasted revenue I have the following calculation.

num(SUM({1<MonthNum ={"<=$(=Num(Month(Today())))"}, Year={"$(=Year(Today()))"}>}[Revenue])

/ SUM({1<MonthNum ={"<=$(=Num(Month(Today())))"}, Year={"$(=Year(Today())-1)"}>}[Revenue])-1,'#,##0.0%')

This gives a ratio of this year YTD revenue vs last year YTD revenue. Now I want to use that ratio into a calculation about 2016.

Say [actual revenue] *

num(SUM({1<MonthNum ={"<=$(=Num(Month(Today())))"}, Year={"$(=Year(Today()))"}>}[Revenue])

/ SUM({1<MonthNum ={"<=$(=Num(Month(Today())))"}, Year={"$(=Year(Today())-1)"}>}[Revenue])-1,'#,##0.0%')

However, if I plot this in a table with a year dimension I get nothing.

1 Solution

Accepted Solutions

Re: Use calculated value into new calculation

May be try this:

[actual revenue] *

If(Year = 2016, Num(Sum(TOTAL {1<MonthNum ={"<=$(=Num(Month(Today())))"}, Year={"$(=Year(Today()))"}>}[Revenue])/Sum(TOTAL {1<MonthNum ={"<=$(=Num(Month(Today())))"}, Year={"$(=Year(Today())-1)"}>}[Revenue])-1, 1),'#,##0.0%')

I have used a static condition of Year = 2016, but you need to determine a more advanced condition for what determines a forecast here?

3 Replies

Re: Use calculated value into new calculation

May be try this:

[actual revenue] *

If(Year = 2016, Num(Sum(TOTAL {1<MonthNum ={"<=$(=Num(Month(Today())))"}, Year={"$(=Year(Today()))"}>}[Revenue])/Sum(TOTAL {1<MonthNum ={"<=$(=Num(Month(Today())))"}, Year={"$(=Year(Today())-1)"}>}[Revenue])-1, 1),'#,##0.0%')

I have used a static condition of Year = 2016, but you need to determine a more advanced condition for what determines a forecast here?

juriengroot
New Contributor III

Re: Use calculated value into new calculation

Thanks! The Total was the key here!

Re: Use calculated value into new calculation

Indeed it was

Community Browser