Qlik Community

Qlik Sense App Development

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

Partner
Partner

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!  
 
 
8 Replies

Re: Adding Value to Blank row in Straight Table

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)))

 

Partner
Partner

Re: Adding Value to Blank row in Straight Table

Hi Sunny,

Removing the zeros did not work.  

Re: Adding Value to Blank row in Straight Table

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

Partner
Partner

Re: Adding Value to Blank row in Straight Table

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!  

Re: Adding Value to Blank row in Straight Table

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))
Partner
Partner

Re: Adding Value to Blank row in Straight Table

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!! 

 

Re: Adding Value to Blank row in Straight Table

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

Highlighted
Partner
Partner

Re: Adding Value to Blank row in Straight Table

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,