topic Re: Help with sum() with filters?? in QlikView

Help with sum() with filters??

qlikuser222 — Fri, 09 Mar 2018 14:22:25 GMT

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

Re: Help with sum() with filters??

vamsee — Fri, 09 Mar 2018 14:57:17 GMT

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

Re: Help with sum() with filters??

sunny_talwar — Fri, 09 Mar 2018 15:01:30 GMT

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

Re: Help with sum() with filters??

qlikuser222 — Fri, 09 Mar 2018 15:10:21 GMT

Hi ,

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

Thanks

Re: Help with sum() with filters??

vamsee — Fri, 09 Mar 2018 15:10:53 GMT

Hi Sunny,

That aggregates all the but his condition is ignored.

Amount>MinAmount. !

Re: Help with sum() with filters??

sunny_talwar — Fri, 09 Mar 2018 15:13:24 GMT

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

Re: Help with sum() with filters??

sunny_talwar — Fri, 09 Mar 2018 15:14:11 GMT

What selections do you have for Minamount and Maxamount?

Re: Help with sum() with filters??

vamsee — Fri, 09 Mar 2018 15:17:16 GMT

oh Okay.. Thanks for clarifying.

Re: Help with sum() with filters??

qlikuser222 — Fri, 09 Mar 2018 15:17:22 GMT

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

Re: Help with sum() with filters??

sunny_talwar — Fri, 09 Mar 2018 15:22:21 GMT

You should be able to use IntervalMatch

Re: Help with sum() with filters??

sunny_talwar — Fri, 09 Mar 2018 15:23:27 GMT

Hahahaha I think it might be better if OP can clarify... I am just saying what I think he wants . You may very well be right and I am the one who is not right

Re: Help with sum() with filters??

vamsee — Fri, 09 Mar 2018 15:43:02 GMT

Haha.. lol lets see.

Re: Help with sum() with filters??

qlikuser222 — Fri, 09 Mar 2018 17:15:01 GMT

Thanks stalwar1‌

Tired interval match but it seem to be not working. Can you please help? please find attcahed !

Thanks

Re: Help with sum() with filters??

qlikuser222 — Fri, 09 Mar 2018 17:16:09 GMT

Attached to the original post test (3) (1)

thanks

Re: Help with sum() with filters??

sunny_talwar — Fri, 09 Mar 2018 17:24:30 GMT

May be this

Main:

LOAD * INLINE [

ID, Amount, Date

1, 5, 12/01/2017

1, 200, 12/02/2017

1, 150, 12/01/2016

1, 300, 11/01/2017

2, 360, 12/12/2017

2, 399, 12/08/2017

2, 40, 1/1/2016

];

Filters:

LOAD * INLINE [

ID, Mindate, MaxDate, MinAmount, Maxamount, Dsic

1, 01/01/2017, 12/31/2017, 10, 400, 4%

2, 01/01/2017, 12/31/2017, 10, 100, 4%

2, 01/01/2017, 12/31/2017, 101, 200, 5%

2, 01/01/2017, 12/31/2017, 201, 300, 5%

2, 01/01/2017, 12/31/2017, 301, 400, 11%

];

Left join (Main)

IntervalMatch(Amount, ID)

LOAD Distinct

MinAmount,

Maxamount,

Resident Filters;

Left join (Main)

IntervalMatch(Date, ID)

LOAD Distinct

Mindate,

MaxDate,

Resident Filters;

Left Join (Main)

LOAD *

Resident Filters;

DROP Table Filters;

Re: Help with sum() with filters??

qlikuser222 — Fri, 09 Mar 2018 17:35:14 GMT

Thanks stalwar1‌

But now in the attached capture, i am getting 8 instead of 8.2

Sorry for the confusion.

Thanks

Re: Help with sum() with filters??

sunny_talwar — Fri, 09 Mar 2018 17:38:09 GMT

In that case, I don't think you should do this in the script...

Re: Help with sum() with filters??

qlikuser222 — Fri, 09 Mar 2018 17:42:20 GMT

Thanks Sunny,

is there any way possible if when user select ID and dates, Qlik triggers select the MinAmount and Maxamounts based on the sum(values).