Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
roisolberg
Creator
Creator

Pivot table - Set analysis Recursion

Hey,

 

 

a bit of background:

each month we take a snapshot of our open backlog (that might have changed to due different changes in orders)

so, we would like to compare the snapshot to the "calculated" (that will take the first snapshot and + Bookings - Revenues)

 

i am looking to create the following table:

 

book-2-bill.JPG

 

to make it easier, i am even ok with setting the first month (jan) with a fixed number

 

the following calculation gives me the correct answer for the first row

 

If(Year=2021 and Month='Jan', SUM(Backlog),
Above(Sum(Backlog))+sum(Bookings)-sum( Revenues))

 

but after that i guess i need some kind of recursion or else it takes the Above(Sum(Backlog))

instead of the entire function

 

any ideas?

 

Many thanks,

Roi

 

1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

Hi,

How about something like;

top(Sum(Backlog))+rangesum(above(Sum(Bookings)-Sum(Revenues),0,rowno()-1))

Shown in here with the parts broken out;

20211203_1.png

Cheers,

Chris.

View solution in original post

2 Replies
chrismarlow
Specialist II
Specialist II

Hi,

How about something like;

top(Sum(Backlog))+rangesum(above(Sum(Bookings)-Sum(Revenues),0,rowno()-1))

Shown in here with the parts broken out;

20211203_1.png

Cheers,

Chris.

roisolberg
Creator
Creator
Author

Thank you Chris! 🙂