Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

etheodorou
New Contributor

Repeat calculation on multiple rows

Hi hoping someone can help me with my dilemma.  I have created an excel report in NPrinting, which is using level to get a calculated column, which also uses Entity which brings in multiple rows.  One section is for budgets but another section will be filled in manually when the recipients receive the report, however I need to subtract the multiple rows to obtain a variance between the budgets and the manually inputting forecast figure.

Ive attached a basic image of the report to try and show what I need to happen.

Would anyone know if this is possible?  Putting in a basic formula in the Entity cell does not carry over to the other cells.

Thanks

Effie

1 Solution

Accepted Solutions
lech_miszkiewic
Honored Contributor III

Re: Repeat calculation on multiple rows

Change it from range to table (select area where you have your table-->Insert-->Table) in excel that wil populate records for each line..

regards

Lech

8 Replies
lech_miszkiewic
Honored Contributor III

Re: Repeat calculation on multiple rows

Change it from range to table (select area where you have your table-->Insert-->Table) in excel that wil populate records for each line..

regards

Lech

etheodorou
New Contributor

Re: Repeat calculation on multiple rows

Hi thanks for a reply but Ive sent through an image of my actual report template, which as you can see is already set out as a table with levels.  I have entered the formula below but as mentioned it doesnt carry through to all the rows.

I dont think you answer will help with my question but thank you anyway.

Effie

Calculated Row 2.png

lech_miszkiewic
Honored Contributor III

Re: Repeat calculation on multiple rows

Hi,

In this case if your Variance area is a "Table" entity in Excel you need to make sure that hte calculation is also populated to the row below of your "=IF(Error(D6-D10,"") which effectively will be =IF(Error(D7-D11,"").


I know that htis will create one additional row so you will have to use "deleterow" functionality in NPritntign to remove it.

see screenshots for referenece

cheers

Lech

regular setup:

1.png

result:

2.png

3. add table and extra expression below

3.png

4. add deleterow functionality

4.png

etheodorou
New Contributor

Re: Repeat calculation on multiple rows

Hi Lech,

It helped but I think the issue is that in the forecast section, data is manually inputted after the report has been sent to the recipients, so it causes a #ref error.

Calculated Row.png

Is there any work around with this fact?

Effie

lech_miszkiewic
Honored Contributor III

Re: Repeat calculation on multiple rows

Now this becomes an Excel exercise... not NPrinting exercise...

What if you put another IF Statement for variance section... like..

If(D10 = 0,"",IFERROR(D6-D10,""))


did you try this?


regards

Lech

etheodorou
New Contributor

Re: Repeat calculation on multiple rows

Hi Lech,

I have tried that also and still getting the same error.

Calculated Row.png

I do think its still an NPrinting issue in that I am getting six rows in the output due to having more than one entity in the template, and as noted before I am using levels not inserting a straight table.  The formula doesnt take into account that there is multiple rows in the final report.  I've tried various work around's but can get it to repeat the calculation for each row.

Regards

Effie

lech_miszkiewic
Honored Contributor III

Re: Repeat calculation on multiple rows

Well, in this case we would have to start over and plan what source you have, what is required output and re-think whole report build.

Maybe you need to take completly different approach - i am not sure.

regards

Lech

etheodorou
New Contributor

Re: Repeat calculation on multiple rows

Thanks Lech I do appreciate your help in this and yes I might need to rethink how to put this one together.

Regards

Effie

Community Browser