Above function with column reference

[sunny_talwar]

I don't think this is the expected behavior, but I might be missing something obvious. I have a calculation in column1 (which has a self referencing component to it)

=If(Year = Min(TOTAL Year), Null(),
	If(Year = Min(TOTAL Year + 1), 
		Above(Sales), 
		RangeSum(Above(Smoothing), (0.5 * (Above(Sales) - Above(Smoothing))))
	)
)

Now this isn't giving me the output I expect and it seems that the self referencing component is what is causing the issue.

If I add Above(Smoothing) or Above(Column(1)) as another column in my straight table, I don't see the right numbers

So, in the example above, I would have expected to see 350 for Above(Smoothing) and Above(Column(1)). How come it is 400?

I also found out that if I remove 0.5 from my calculation... it fixes the issue

=If(Year = Min(TOTAL Year), Null(),
	If(Year = Min(TOTAL Year + 1), 
		Above(Sales), 
		RangeSum(Above(Smoothing), (0.5 * (Above(Sales) - Above(Smoothing))))
	)
)

So, how is this multiplication impacting my above() function.

@tresesco, @marcus_sommer, @rwunderlich, @Gysbert_Wassenaar, @swuehl

[marcus_sommer]

Hi Sunny,

just disable the hide NULL option in tab presentation or change the chart-type to pivot and it will work. Why, mhhh ... I'm not really sure. But it has something to do with the way how the visible chart-object is rendered respectively connected with the virtual table in the background on which the calculations are performed.

After writing this I remember that I had once or twice situations in which a straight-table returned wrong/unexpected results and a change to a pivot solved it but I could always live with the pivot and never dived deeper to find an explanation.

- Marcus

[mdmukramali]

Dear Sunny,

Thanks for the help.

i adjusted the formula little bit and it's working perfectly.

even i changed some criteria like for the first year the Smoothing should be equal to Sales.

// Working Syntax 1

=If(Year = Min(TOTAL Year), Sum(Sales),
            RangeSum(Above(Smoothing) ,(0.5 * (Above(Sales) - Above(Smoothing))))
    )

or 

=If(Year = Min(TOTAL Year), Sum(Sales),
            RangeSum(Above(Smoothing) + (0.5 * (Above(Sales) - Above(Smoothing))))
    )

// Working Syntax 2

=If(Year = Min(TOTAL Year), Sum(Sales),
              If(Year = Min(TOTAL Year + 1), Above(Sales),
           RangeSum(Above(Smoothing), (0.5 * (Above(Sales) - Above(Smoothing))))
     ))

OR 

 =If(Year = Min(TOTAL Year), Sum(Sales),
              If(Year = Min(TOTAL Year + 1), Above(Sales), 
           RangeSum(Above(Smoothing)+ (0.5 * (Above(Sales) - Above(Smoothing))))
     ))

Kindly find the attached file.

Thanks,

Mohammed Mukram 

[mdmukramali]

Dear Sunny,

your solution is working just i disabled the suppress zero values in presentation tab.

[sunny_talwar]

Awesome, I am glad it worked.