Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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


Thanks in Advance

3 Replies
MVP
MVP

Re: Sumif Function in Qlikview

May be this:

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

Not applicable

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?!!

MVP
MVP

Re: Sumif Function in Qlikview

May be try this:

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