topic Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working in QlikView

Pivot Table / Straight Table Expression Help - RangeMax Not Working

pnn44794 — Sat, 12 Oct 2019 23:37:27 GMT

Hello:

I have both a straight table and a pivot table that.

Any ideas? As always, any and all help is appreciated.

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

sunny_talwar — Mon, 11 Dec 2017 20:30:55 GMT

May be, just may be, you want this

Num(Sum(RangeMax("FB_Finance Confirmed 2017 Savings", 0)),'$#000.0')

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

pnn44794 — Sat, 12 Oct 2019 23:41:23 GMT

Ok. I tried it without the beginning and ending double quotes and reloaded. Total Saves is now blank. No values at all.

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

pnn44794 — Mon, 11 Dec 2017 21:38:35 GMT

Interesting. When I paste your suggestion, Sonny, into a Text Object, it works.

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

sunny_talwar — Mon, 11 Dec 2017 21:56:45 GMT

Parry my name is Sunny (not Sonny), just like yours is Perry and not Parry ...

I don't have too much information, but I think the problem is related to dollar sign expansion which is evaluated once per chart... I might be wrong here, but without having a look at a sample (like always) it is aiming for the target in dark

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

pnn44794 — Mon, 11 Dec 2017 22:02:44 GMT

Sorry Sunny for the typo. I know it’s Sunny. No offense meant. I’ll get some sample data together and will post it in a little while.

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

sunny_talwar — Mon, 11 Dec 2017 22:04:47 GMT

None taken my friend . I didn't mean to offend, but I don't understand why people some time call me Sonny or Sunni when my name clearly says Sunny

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

pnn44794 — Mon, 11 Dec 2017 22:12:56 GMT

I truly fat fingered it. Tired. I need to get some sleep, but I need to get this and a few other things working.

BTW, the straight table is now totaling up as expected using "Num(RangeMax(Sum("FB_Finance Confirmed 2017 Savings"), 0),'$#,000')" from within the load script. This is very weird. So now it’s just the Pivot table where RangeMax is including negative values. Again, I’ll get some sample data together. Thanks.

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

pnn44794 — Tue, 12 Dec 2017 14:21:53 GMT

Hello Sunny (and anyone else who may be following this discussion thread). Any ideas why I might be picking up the negative values with RangeMax?

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

pnn44794 — Tue, 12 Dec 2017 15:05:32 GMT

I figured it out. Sunny your potential solution of Num(Sum(RangeMax("FB_Finance Confirmed 2017 Savings", 0)),'$#000.0') was very close.

Here's what I was using within my Load script and which was including the negative values:

"Num(RangeMax(Sum("FB_Finance Confirmed 2017 Savings"), 0),'$#,000')" <-Double quotes are needed as this is an expression for a user customizable chart

Here' what works and which excludes the negative values. What I changes is highlighted in red:

"Num(Sum(RangeMax(0, "FB_Finance Confirmed 2017 Savings")),'$#,000')" <--I put Sum in front of RangeMax (Sunny's suggestion) and moved the 0 to the first position/parameter for RangeMax

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

sunny_talwar — Tue, 12 Dec 2017 15:50:27 GMT

Hahahahaha, are you sure that just moving 0 ahead of the field worked? From what I know, these two things are the exact same thing

1) RangeMax("FB_Finance Confirmed 2017 Savings", 0)

2) RangeMax(0, "FB_Finance Confirmed 2017 Savings")

There is no difference whatsoever

Re: Pivot Table / Straight Table Expression Help - RangeMax Not Working

pnn44794 — Tue, 12 Dec 2017 16:05:49 GMT

Yep. I get the correct Total Saves amount now in both the straight table and the pivot table. As always, thank you for your help. I appreciate it.