Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis

Hi all

i have a table as follow, it has so many rows but here i bring 3 rows as example:

BranchChannelInvoice No.Invoice DateInvoice Due DateAccounts Receivable

A

Retail101/01/201760 days after invoice date10
AHospital201/01/201790 days after invoice date30
AReferral301/01/2017120 days after invoice date50

i want to create a measure or expression for overdue.

our company has a rule for what to be called overdue as follow:

if channel is Retail and it has passed more than 30 days from Invoice due date it would be overdue

if channel is Referral and it has passed more than 120 days from Invoice date it would be overdue

if channel is Hospital and it has passed more than 150 days from Invoice date it would be overdue


Now i want to have an expression for overdue, which if i choose retail it shows me just retail overdue.

if i use set analysis and filter channel, it always shows me all overdue for all channel

am i suppose to use set analysis,How?

thanks in advance

1 Solution

Accepted Solutions
sunny_talwar

Try this

Sum({$<Channel *= {Retail}, [Invoice Due Date] ={"=Today() - [Invoice Due Date] > 30"}>+$<Channel *= {Hospital}, [Invoice Due Date] ={"=Today() - [Invoice Due Date] > 150"}>+$<Channel *= {Referral}, [Invoice Due Date] ={"=Today() - [Invoice Due Date] > 120"}>}Accounts Receivable)

View solution in original post

12 Replies
sunny_talwar

What is your desired output for the 3 rows you have provided above?

Not applicable
Author

If today is based, 10 for Retail and 10 for All channel is the output.

if 04/01/2017 is based, 10 for Retail, 30 for Hospital and 40 for All channel.

sunny_talwar

How will Retail show 10? Today is 3/16 and Invoice Due date is 3/2/2017 (1/1/2017 + 60 days).... If for retail, the overdue date is Invoice Due date + 30 days, then it isn't due until 4/2/2017 I think

Not applicable
Author

Yes you are right, my mistake but you know what i mean

my problem is if i use channel in my dimension i cant use filter

sunny_talwar

Why not?

Not applicable
Author

Actually i can but because of my formula when i filter channel on retail it shows me sum of all overdue, i think my problem is my formula. for every channel i write one set analysis and i think its wrong

sunny_talwar

Would you be able to share a sample where we can see your problem?

Not applicable
Author

Sum({$<Channel={Retail},Measure A={'>30'}>+$<Channel={Hospital},Measure B={'>150'}>+$<Channel={Referral},Measure C={'>120'}>}Accounts Receivable)

Measure A=Today- Invoice Due Date

Measure B=Today- Invoice Date



sunny_talwar

Is this the solution? I am not sure if you are asking me something... but you can try this

Sum({$<Channel={Retail}, [Invoice Due Date] ={"=Today() - [Invoice Due Date] > 30"}>+$<Channel={Hospital}, [Invoice Due Date] ={"=Today() - [Invoice Due Date] > 150"}>+$<Channel={Referral}, [Invoice Due Date] ={"=Today() - [Invoice Due Date] > 120"}>}Accounts Receivable)