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: 
asmithids
Partner - Creator II
Partner - Creator II

Adding Value to Blank row in Straight Table

Hello,

I have a straight table where a row of a measure does not have activity for that period.  I need to insert the value from the above row whenever a field is missing a value in a row.  Please see attached screenshot for an example.  

Below is the expression I am trying to use to insert the above value into the blank field just below.  The expression has the Above() function, but it's not working.  

If(Sum({<ReserveType={'Indemnity'}>}ReserveAmount)=0,Above(Sum({<ReserveType={'Indemnity'}>}ReserveAmount),0),
If(isNull(Sum({<ReserveType={'Indemnity'}>}ReserveAmount)),
    Above(Sum({<ReserveType={'Indemnity'}>}ReserveAmount),0),Sum({<ReserveType={'Indemnity'}>}ReserveAmount)))
        
Thank you in advance for any assistance with this issue!  
 
 
Labels (4)
8 Replies
sunny_talwar

Not sure, but may be try to remove , 0 for Above() function

If(Sum({<ReserveType={'Indemnity'}>}ReserveAmount)=0,Above(Sum({<ReserveType={'Indemnity'}>}ReserveAmount)),
If(isNull(Sum({<ReserveType={'Indemnity'}>}ReserveAmount)),
    Above(Sum({<ReserveType={'Indemnity'}>}ReserveAmount)),Sum({<ReserveType={'Indemnity'}>}ReserveAmount)))

 

asmithids
Partner - Creator II
Partner - Creator II
Author

Hi Sunny,

Removing the zeros did not work.  

sunny_talwar

Would it be possible to share a sample to check this out?

asmithids
Partner - Creator II
Partner - Creator II
Author

I think I found the issue.  It's in the load script.  I attached a copy of the qvf and screen shot .  The issue is as follows:

The Adjuster Fees & TPA Fees get combined into the Reserve Type called “Fees”. The ReserveAmtRemaining is the running balance as of the period. The issue is since the ReserveAmtRemaining amount is a running balance as of the period, the two types of Reserve Classification History need to be combined into the ReserveType “Fees” for the amount of 1748.85 as of 2019-Mar. Which is the 344.50+1404.35. Notice that the 1404.35 is in a different period. The two Reserve Classification History of Adjuster Fees & TPA Fees always need to be combined into the most current period. The attached shows how the grouping needs to be.

Essentially, whatever Reserve Classification History ID is in a current period (i.e. Adjuster Fees or TPA Fees), the other whether in the same period or in a prior period needs to be added to the current period.  This new combined number needs to be in its own field.  The Indemnity Reserve Classification History ID is one of a kind so there is no need to look back.  

I thought about using the vlookup function in the load script as I don't think the Below() or Previous() functions will work as the look back is not a consistent number of rows down of which to get data.  

Hope the narrative was clear.   

Thank you again for your help!  

sunny_talwar

Try this

If(ReserveType = 'Fees',
Aggr(
If(Above(TOTAL ClaimID) = ClaimID and Above(TOTAL ReserveType) = ReserveType, Above(TOTAL Sum(ReserveAmtRemaining)), 0)
, ClaimID, (ReserveType, (TEXT, DESC)), (YearMonth, (NUMERIC)), ([Reserve Classification History], (TEXT, DESC))) + Sum(ReserveAmtRemaining),
Sum(ReserveAmtRemaining))
asmithids
Partner - Creator II
Partner - Creator II
Author

The table code you provided works perfectly!!!  You are a Rockstar!!! 

The app I'm trying to complete has reserve KPIs.  See attached screen shot.  How can I translate the table coding to the load script so I can create reserve measures that users can pull into KPIs or another visualization (i.e. pie, line, bar)? 

Thank you again Sunny!!  You've been a tremendous help!! 

 

sunny_talwar

What are the numbers you are hoping to see the KPI object or a chart (what dimensions for the chart)?

asmithids
Partner - Creator II
Partner - Creator II
Author

Sunny,

I need the reserve fees and indemnity numbers to index to all of the dimensions in the dashboard just like all of the other measures.  I sent you a private message.  Did you see it?   

Thank you,