Help with sum() with filters??

[qlikuser222]

Hi All,

I have a requirement where i need to sum() and its Discount with the date and amount filters. i tried if condition but results are not as expected. Please help me to understand the logical way to do it.

Please find attached which shows the example.

Thanks

[vamsee]

Hi Nicholas,

In your expression, the mentioned condition is

              Date >= Mindate

But where as the you have multiple dates as filter.

I have added date as dimension and your chart looks right.

Please check.

Thanks.

Vamsee

[sunny_talwar]

May be this

=Sum(Aggr(if(Sum(TOTAL <ID> Amount)>=MinAmount and Sum(TOTAL <ID> Amount)<=Maxamount, if(Date >= Mindate and Date <= MaxDate, sum(Amount) *  Dsic)), ID, Date))

[qlikuser222]

Hi ,

Thanks alot it works, but when i select ID 2 , it throws null, unable to understand why.

Thanks

[vamsee]

Hi Sunny,

That aggregates all the but his condition is ignored.

Amount>MinAmount. !

[sunny_talwar]

I think the condition is at the ID level, not at the ID and Date level

[sunny_talwar]

What selections do you have for Minamount and Maxamount?

[vamsee]

oh Okay.. Thanks for clarifying.

[qlikuser222]

Thanks got it, Is there any way this can be done in the back end, users are not suppose to select min and Max amounts and dates and in this case it always gives null.

Thanks

[sunny_talwar]

You should be able to use IntervalMatch