topic Re: Set analysis in QlikView

Set analysis

Thu, 16 Mar 2017 12:09:07 GMT

Hi all

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

Branch	Channel	Invoice No.	Invoice Date	Invoice Due Date	Accounts Receivable
A	Retail	1	01/01/2017	60 days after invoice date	10
A	Hospital	2	01/01/2017	90 days after invoice date	30
A	Referral	3	01/01/2017	120 days after invoice date	50

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

Re: Set analysis

sunny_talwar — Thu, 16 Mar 2017 12:12:54 GMT

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

Re: Set analysis

Thu, 16 Mar 2017 12:21:36 GMT

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.

Re: Set analysis

sunny_talwar — Thu, 16 Mar 2017 12:29:26 GMT

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

Re: Set analysis

Thu, 16 Mar 2017 14:32:56 GMT

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

Re: Set analysis

sunny_talwar — Thu, 16 Mar 2017 14:48:06 GMT

Why not?

Re: Set analysis

Thu, 16 Mar 2017 20:45:58 GMT

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

Re: Set analysis

sunny_talwar — Fri, 17 Mar 2017 08:21:28 GMT

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

Re: Set analysis

Sat, 18 Mar 2017 05:34:42 GMT

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

Re: Set analysis

sunny_talwar — Sat, 18 Mar 2017 12:21:56 GMT

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)

Re: Set analysis

Sat, 18 Mar 2017 13:02:18 GMT

No it's not the solution, it's the formula i am using. it's pretty similar to what you write.

even when i use your formula it always shows me total Overdue!

here is my table when i use no filter on my channel:

here is my table when i filter my channel on Retail:

you can see now my Accounts Receivable field shows me only Retail and Overdue Total!!

I want to see only retail overdue

i hope you can help me on this

Thanks again

Re: Set analysis

sunny_talwar — Sat, 18 Mar 2017 15:49:35 GMT

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)

Re: Set analysis

Sat, 25 Mar 2017 05:12:38 GMT

Thanks Sunny, it works like a charm