topic Re: Above function with column reference in QlikView

Above function with column reference

sunny_talwar — Sat, 16 Nov 2024 21:47:47 GMT

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.

@tresB, @marcus_sommer, @rwunderlich, @Gysbert_Wassenaar, @swuehl

Re: Above function with column reference

marcus_sommer — Fri, 30 Nov 2018 13:24:07 GMT

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

Re: Above function with column reference

mdmukramali — Fri, 30 Nov 2018 13:38:54 GMT

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))))
)

=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))))
))

=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

Re: Above function with column reference

mdmukramali — Fri, 30 Nov 2018 13:37:00 GMT

Dear Sunny,

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

Re: Above function with column reference

sunny_talwar — Fri, 30 Nov 2018 13:48:43 GMT

Awesome, I am glad it worked.