topic Prognosis calculation in Qlik Sense in App Development

Prognosis calculation in Qlik Sense

bwisealiahmad — Fri, 02 Dec 2016 13:46:57 GMT

Hey guys.

So I have a budget table with:

BudgetDate

Actual

Budget

DayThisWeek

WeekDay

DayThisMonth

In a different table I have "PaidOutAmount"

What I am trying to do is calculate a prognosis based on dividing Sum of PaidOutAmount by the # of day this month and then multiply by the Max Amount of Days This Month.

Here is a picture of the Budget Table:

So for today (02.2012.2016) it would be:

(Sum(PaidOutAmonut) / (Value which is on that row from "DagDenneMåneden"))

Max(DagDenneMåneden) - (Value which is the highest this month from "DagDenneMåneden)

Now what I am trying to understand is how can I get the value which is on the row in "DagDenneMåneden" from Todays date so that the prognosis grows as this increases? Monday the value would 3 instead of 2 and prognosis would be adjusted accordingly.

Suggestions?

Re: Prognosis calculation in Qlik Sense

sunny_talwar — Fri, 02 Dec 2016 14:07:30 GMT

Not sure I completely understand, do you have a sample you can share?

Re: Prognosis calculation in Qlik Sense

bwisealiahmad — Fri, 02 Dec 2016 14:13:28 GMT

So the example calculation is:

(Sum(PaidOutAmount))/Max(DaysThisMonthUntilThisDate] )* Max ([DaysThisMonth)

My issue is that I don't know how to get the value which is the highest # of days on the current day (today) into the expression. The only field I have is DaysThisMonth but if I use the Max for that it would be.

(Sum(PaidOutAmount) / 22)

the correct calculation would be (for todays date)

Sum(PaidOutAmount / 2)

but on Monday it would be

Sum(PaidOutAmoiunt / 3)

Makes sense?

Re: Prognosis calculation in Qlik Sense

sunny_talwar — Fri, 02 Dec 2016 14:25:53 GMT

How about this?

Max({<PaidOutAmount = {'*'}>}[DaysThisMonth)

Re: Prognosis calculation in Qlik Sense

sunny_talwar — Fri, 02 Dec 2016 14:26:33 GMT

Max({<DaysThisMonth= {"=Sum(PaidOutAmount) > 0"}>}[DaysThisMonth)

Re: Prognosis calculation in Qlik Sense

bwisealiahmad — Fri, 02 Dec 2016 14:29:38 GMT

It's almost as if I need a mix of Max(DaysThisMonth) and a CYTD Flag I think.

Re: Prognosis calculation in Qlik Sense

sunny_talwar — Sat, 03 Dec 2016 03:12:00 GMT

So none of the above expressions worked for you?