topic Re: Negitives in Straight Table in QlikView

Negitives in Straight Table

Fri, 04 Oct 2013 13:00:28 GMT

Is there a way to limit a straight table to show only the negative results from an expression within the straight table?

Update with more details....

I have the following expressions:

Yr Potential Pay Impact: If(Target-[Adjusted Actual]<0,Target-[Adjusted Actual],0)

Target: WorkRVU+[Contract RVUs])*Multiplier)/[Month Number])*12

Adjusted Actual: Wage-Wage_Admin

There is also a flag to say if an employee has been with the company for 2 years

What I need to show in a straight table is only the employees whose [Yr Potential Pay Impact] <0 AND 2yr_Flag =No

Sorry for the poorly spelled out first attempt at a question, apparently my brain thinks its already the weekend!

Thanks for your help!

Re: Negitives in Straight Table

rustyfishbones — Fri, 04 Oct 2013 13:01:36 GMT

Do it in the expression.

Like SUM(Sales) > 0

Re: Negitives in Straight Table

Fri, 04 Oct 2013 13:04:02 GMT

LOL... thanks, I don't think I explained myself well. I might have to think about it and explain my question a little better

Re: Negitives in Straight Table

bbi_mba_76 — Fri, 04 Oct 2013 13:04:40 GMT

You could use an if in the expression and suppress the dimension when value is null

=if(sum(Sales)<0, sum(Sales)

Re: Negitives in Straight Table

rustyfishbones — Fri, 04 Oct 2013 13:10:56 GMT

ha! no problem.

I had the Expression wrong anyway

it should be

< 0

Re: Negitives in Straight Table

srchilukoori — Fri, 04 Oct 2013 13:18:20 GMT

use the expression:

IF(Sum(<Value>) < 0, Sum(<Value>), 0)

and suppress the zero values in the presentation tab.

Re: Negitives in Straight Table

bbi_mba_76 — Fri, 04 Oct 2013 13:21:38 GMT

You could use a calculated dimension to filter the rows you need and then hide this column in the presentation tab.

Re: Negitives in Straight Table

Fri, 04 Oct 2013 13:24:02 GMT

I have tried similar logic but I keep getting error messages from QlikView.

The expression I have entered is:

((Sum([Yr Potential Pay Impact])<0) and (Hire_Age='New'),[Yr Potential Pay Impact],0)

When including the expression and field names I am using the prepopulated drop down and I am getting a 'Bad Field Name Error' on Yr Potential Pay Impact. I don't understand how its a bad field name if I am selecting it out of the list?!

Re: Negitives in Straight Table

Fri, 04 Oct 2013 13:26:43 GMT

QlikView is not allowing me to use the Expression Name in a calculated Dimension

Re: Negitives in Straight Table

bbi_mba_76 — Fri, 04 Oct 2013 13:38:27 GMT

I suppose first you have to define the dimension and then the expressions in a chart.

If I understand well Yr Potential Pay Impact, Target and Adjusted Actual are the expressions you defined in the straight table, so they are not available as calculated dimension.

Re: Negitives in Straight Table

Fri, 04 Oct 2013 13:42:52 GMT

Correct, all three of the expression I listed above are in the straight table. I am just trying to limit the view of the straight table to where Yr Potential Pay Impact is negative (the 2Year flag I am not terribly worried about, it should be easy to include)

Re: Negitives in Straight Table

bbi_mba_76 — Fri, 04 Oct 2013 13:48:28 GMT

Then you could add a calculated dimension with

If(2yr_Flag =No, employees)

and then hide this column in the presentation tab.

Then you could use the dimension limits tab to restrict the values to show only values that are less than 0 exact amount. In this way you could use the first expression to filter.

Re: Negitives in Straight Table

Fri, 04 Oct 2013 13:53:37 GMT

I am not sure I understand, I think that would only solve the 2yr_Flag piece. I am still unsure of how to show only the negitives for Yr Potential Pay Impact.

Sorry if i am making it more complicated then it needs to be!

Re: Negitives in Straight Table

bbi_mba_76 — Fri, 04 Oct 2013 14:03:13 GMT

I loaded this table

where we have a dimension Dim, a flag, a measure and Abs to calculate negative values.

Using a calculated dimension I apply the first filter

Then I use the dimension limits

Re: Negitives in Straight Table

Fri, 04 Oct 2013 15:42:57 GMT

Thats pretty cool!! Thanks for your help