Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bwisealiahmad
Partner - Specialist
Partner - Specialist

Prognosis calculation in Qlik Sense

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:

budg.JPG

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?

6 Replies
sunny_talwar

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

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

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)

*

22

the correct calculation would be (for todays date)

Sum(PaidOutAmount / 2)

*

22

but on Monday it would be

Sum(PaidOutAmoiunt / 3)

*

22

Makes sense?

sunny_talwar

How about this?

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

sunny_talwar

Or

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

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

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

sunny_talwar

So none of the above expressions worked for you?