Qlik Community

Ask a Question

App Development

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

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
vamshi_1241
Partner
Partner

Need to repeat previous cell values in Qliksense Pivot table

Hi Team,

I have tried multiple ways to repeat the previous cell values if the current cell is null in the Qliksense pivot table but could not achieve it. Attached sample qvw. I have a Test, Date, and Amount field in the source table and here is the business requirement,

1. Test A and D have data for only a few dates so whenever these have 0's, the previous amount should be repeated into those cells but should not cumulative amount if the next date has data.

Let say - Test A has amount 100 on 1st, 200 on 5th and 500 on 9th Jan. Hence, 2nd to 4th would be 100 instead of 0 and from 5th to 8th Jan, amount should be 200 instead of cumulative 300. My current expression is not giving this result.

2. Test B be should show cumulative numbers in the report.

3. Test C will show actual values.

 

1 Solution

Accepted Solutions
vamshi_1241
Partner
Partner
Author

Thanks for all your help, below expression is worked for me -

if((Test = 'A' or Test = 'D') and Sum(Amount) = 0, before(TOTAL column(1)),Alt(sum(fabs(Amount)), 0))

View solution in original post

5 Replies
brunobertels
Specialist II
Specialist II

Hi 

See this post : 

https://community.qlik.com/t5/New-to-Qlik-Sense/Opening-Balance/td-p/1734963 

 

And example below from Gabriel

https://www.dropbox.com/sh/v5twzlr58gvt51t/AADR9Rwib4iPb1CPnU8HJP9Ja?dl=0 

You will find how to populate null values and repeat previous ones 

 

Hope it helps 

Regards 

vamshi_1241
Partner
Partner
Author

Thanks for your quick response but it is not because of missing data. I have gone through the qvf attached in the dropbox but that just give us cumulative data. I have to show the previous data (which is >0) until the next data comes in the next date column.

PriyankaShivhare
Creator II
Creator II

For Previous value- if(len(TestA)>0,TestA,peek('TestNew')) as TestNew

for Cumulative- if(peek(Dim1)=Dim1, rangesum(peek('Cumulative'),TestB ),TestB ) as Cumulative;

vamshi_1241
Partner
Partner
Author

can we have this solution from UI itself?

vamshi_1241
Partner
Partner
Author

Thanks for all your help, below expression is worked for me -

if((Test = 'A' or Test = 'D') and Sum(Amount) = 0, before(TOTAL column(1)),Alt(sum(fabs(Amount)), 0))

View solution in original post