Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

May be this:

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

Not applicable
Author

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

sunny_talwar

May be try this:

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