Sumif Function in Qlikview

Thu, 12 Jan 2017 12:54:41 GMT

Hi Everyone,

I want to create a sumif function in qlikview like excel.here is my data,the formula i use in excel and my formula in qlikview.

i have a sales table like this(of course with a lot more information):

Branch	Channel	Invoice number	Difference from Invoice date till today(Day)	Accounts Receivable (Sales)
A	Retail	1	35	1000
A	Hospital	2	158	10000
B	Retail	5	15	200
B	Hospital	7	180	20000
C	Retail	11	50	500
C	Hospital	15	120	25000

I have some rules to create Overdue for this channel; in retail i only want to sum those which column 4 is above 30 days and in hospital above 150 days. in excel i write my formula like this:

if(and(channel="Retail",Days>30),sum(Sales),if(and(channel="Hospital",Days>150),Sum(Sales),0))

Now in Qlikview, i tried using set analysis like this:

sum( {$<Channel={Retail},Days={'>30'}>}Sales)

sum( {$<Channel={Hospital},Days={'>150'}>}Sales)

but i have to create two expression and for example if i want to create a pivot table, for subtotal i always have zero and i think its because of in total i don't have one dimension(Channel)

I would be grateful if anyone can help me on this issue

Thanks in Advance

Re: Sumif Function in Qlikview

sunny_talwar — Thu, 12 Jan 2017 16:35:19 GMT

May be this:

Sum({$<Channel={Retail},Days={'>30'}>+$<Channel={Hospital},Days={'>150'}>}Sales)

Re: Sumif Function in Qlikview

Sat, 14 Jan 2017 04:40:12 GMT

Thank you Sunny T, it works great in my pivot table however if i filter my channel(Dimension) it shows me total overdue and there is no difference. what should i do about this?!!

Re: Sumif Function in Qlikview

sunny_talwar — Sun, 15 Jan 2017 23:24:06 GMT

May be try this:

Sum({$<Channel *= {Retail}, Days={'>30'}>+$<Channel *= {Hospital},Days={'>150'}>}Sales)

topic Re: Sumif Function in Qlikview in QlikView

Sumif Function in Qlikview

Re: Sumif Function in Qlikview

Re: Sumif Function in Qlikview

Re: Sumif Function in Qlikview