Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
alexandernatale
New Contributor III

Final balance vs forecast

Hello,

I have a problem with the comparison between the final data and the forecast. I have 2 tables so structured.
For the final budget I have:


month | year | headquarters | final date



For the forecast I have:


month | year | headquarters | provisional data



I wanted to create a KPI that showed me the forecast at the end of the year, adding the final data to date (October) and adding the forecast data to conclude the year.


I tried with:


Sum({<year={2018}, Month>{$(= Max (Month))}>} [Provisional data])+Sum({<year={2018}) [final date])


...but don't work.


Thanks.

1 Solution

Accepted Solutions
Partner
Partner

Re: Final balance vs forecast

Did you try with quotes?

Month={">$(= Max (Month))"}

View solution in original post

5 Replies
Partner
Partner

Re: Final balance vs forecast

maybe this

Sum({<year={2018}, Month={>$(= Max (Month))}>} [Provisional data])+Sum({<year={2018}>}[final date])

alexandernatale
New Contributor III

Re: Final balance vs forecast

thank you, Mary for the answer but don't work.

I tried to divide the formula and clearly the part that does not work is that relative to the provisional data. There is something that escapes me with the max function or in the use of the major symbol (>).

Partner
Partner

Re: Final balance vs forecast

Did you try with quotes?

Month={">$(= Max (Month))"}

View solution in original post

alexandernatale
New Contributor III

Re: Final balance vs forecast

Yes, yes, yes!!! it's correct!!!!

There is another little problem. If I don't select the months, the value of KPI is egual to the sum of [final date] . If I selected ALL the months, the KPI it's ok. Why?

Partner
Partner

Re: Final balance vs forecast

Because max (Month) will give you max of all the Months not just the actual values

i.e. if you have actual data till Oct. And Projections till Dec. As Month refers to both tables. Max(Month) gives value as Dec

You need redesign with flags or some other means to identify the months where actual data exists