topic peek with If condition in QlikView

peek with If condition

upaliwije — Fri, 17 Aug 2018 10:40:46 GMT

Hi Friends

I have developed the attached QVW file with help from the friends in community. Now I have come the final stage where I do not get the expected output. In otherwords in my data table if the Two Policy nos match and Monthend period match and NEP+NEP (of two policies )is zero NEP amount sholud show Zero in both policies(rows). I have attched my QV docoment. Kindly look into this and help me,

Current output report

Required Output Report

Re: peek with If condition

vamsee — Fri, 17 Aug 2018 15:35:13 GMT

Hello,

If you are looking for something like this

In the sheet Test of your QVW
remove the dimensions
- Ref Number
- Trans Date
In the presentation tab of your Pivot table Uncheck suppress zero values

If you want to keep the dimensions and see 0's then you might have to do the summation in the script itself.

Re: peek with If condition

upaliwije — Sat, 18 Aug 2018 06:03:06 GMT

‌ thanks.

i Need both those field in my pivot table. If you can do with two fields pls help me.

Re: peek with If condition

petter — Sat, 18 Aug 2018 08:00:31 GMT

If you substitute the Sum(NEP) expression in your test tab in the pivot table with this:

If(If(Odd(RowNo(TOTAL)),Sum(NEP)+Below(TOTAL Sum(NEP)),Above(TOTAL Sum(NEP))+Sum(NEP))=0,0,Sum(NEP))

You will get this:

I also set the Populate Missing Cells to nothing in the Presentation tab:

Re: peek with If condition

petter — Sat, 18 Aug 2018 08:07:07 GMT

Or you might prefer a more accurate display of the values versus the missing values by using this expression:

If(If(Odd(RowNo(TOTAL)),Sum(NEP)+Below(TOTAL Sum(NEP)),Above(TOTAL Sum(NEP))+Sum(NEP))=0,Null(),Sum(NEP))

To get this:

By setting this:

Re: peek with If condition

upaliwije — Sat, 18 Aug 2018 13:08:02 GMT

‌Thanks Petter.

IT solves my issue except does not show values in total column in some rows. If you can pls also give a solution for that also.

THanks

Re: peek with If condition

petter — Sat, 18 Aug 2018 23:04:30 GMT

I am not able to find a way to calculate the correct total with the Pivot Table. However it is easy to do it with a Straight Table instead:

And to get the straight table's expressions and labels to be easier to handle I created these three variables:

vMonthEnd, vNEP and vTotal. They all take parameters as to how many months ago the expression should be calculated for.

Also a small helper table to get the months dynamic is needed. I just called it Cal. See attached QVW.

Re: peek with If condition

upaliwije — Sun, 19 Aug 2018 13:49:18 GMT

Thanks a lot Petter,

This is excatly what I was looking for. But If I am not disturbing you pls help me with futher development I require on the output report. In this report I want to show one row (postive or negative) which has higher value. I have shown my requrement in image below. pls also pay your attention tovalue in total column. Appreciate If you kindly help me .

Re: peek with If condition

petter — Sun, 19 Aug 2018 16:29:03 GMT

Maybe you should close this thread now - continously raising the bar is not how this forum is supposed to work

Re: peek with If condition

upaliwije — Mon, 20 Aug 2018 03:00:09 GMT

Sorry If I disturb you. Thank you for your great help

Re: peek with If condition

petter — Mon, 20 Aug 2018 06:49:59 GMT

It is doable like you can see here. But the expressions and the tweaking that is necessary really make me think that we are stretching the limits on how far one should go to emulate a Spreadsheet oriented way of calculating in QlikView. It is much better to rethink the calculation model and come up with a model that is natural for QlikView's way of working.