3 Replies Latest reply: Jan 15, 2017 6:24 PM by Sunny Talwar

# Sumif Function in Qlikview

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

BranchChannelInvoice numberDifference from Invoice date till today(Day)Accounts Receivable (Sales)
ARetail1351000
AHospital215810000
BRetail515200
BHospital718020000
CRetail1150500
CHospital1512025000

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

• ###### Re: Sumif Function in Qlikview

May be this:

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

• ###### Re: Sumif Function in Qlikview

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

May be try this:

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