Qlik Community

QlikView Publisher

Discussion Board for collaboration on QlikView Publisher.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mellerbeck
Contributor II

Convert chart formula in order to be able to display total in text box

For an accounting straight table I have

if(sum({1}total <BillTo> [First Exclusion]) / sum({1}total <BillTo> [TotalDue])>0.2 AND BBC='-' AND [Residual Short Pays]=0 ,sum(CurrentDue + DaysPast0To30 + DaysPast31To60),0)

Basically if the sum of all the [First Exclusion] divided by the sum of all of the TotalDue is greater than .20 then pass through the sum of currentDue + DaysPast0To30 + DaysPast31To60

I tried

sum(if(sum({1}total <BillTo> [First Exclusion]) / sum({1}total <BillTo> [TotalDue])>0.2 AND BBC='-' AND [Residual Short Pays]=0 ,CurrentDue + DaysPast0To30 + DaysPast31To60,0))

And I messed with aggr, but no luck

Any thoughts and pointers much appreciated!

-Michael

Tags (1)
3 Replies
mellerbeck
Contributor II

Convert chart formula in order to be able to display total in text box

Ahh I got it

=sum(aggr( if( sum( total <BillTo> [First Exclusion])/ sum( total <BillTo> TotalDue) >.2 AND BBC='-' AND [Residual Short Pays]=0, sum( CurrentDue + DaysPast0To30 + DaysPast31To60),0),BillTo))

But now I'm trying to make it so that it will ignore selections. It's resisting my efforts

MVP
MVP

Convert chart formula in order to be able to display total in text box

You may need the {1} in more places than you expect.  You'd need sum({1} aggr(sum({1}... for instance.  Basically, stick your set expression on every aggregation function.  Mind you, you might already be doing that.  It's just a common error I see, and sometimes still make.

mellerbeck
Contributor II

Convert chart formula in order to be able to display total in text box

Have you ever seen where it doesn't work even if you use {1} most every where

=sum({1} aggr(if( sum({1} total <BillTo> [First Exclusion])/ sum({1} total <BillTo> TotalDue) >.2 AND BBC='-' AND [Residual Short Pays]=0, sum({1} CurrentDue + DaysPast0To30 + DaysPast31To60),0),BillTo))

It looks like it might need to be after the aggr as well? but the syntax highlighter doesn't seem to care for it and it still doesn't ignore selections. oh well not critical, just trying to figure it out

Community Browser