topic Re: set analysis with a subtraction in QlikView

set analysis with a subtraction

Tue, 19 Feb 2013 17:39:36 GMT

Hey guys. Still new to QV, and I have a question for you. Fairly easy, I'm assuming.

I'm trying to build a chart, to show the values of one field minus another field. I'd also like this to be presented in a percentage.

basically, it's:

apel_ltr_snt_dt - date_received

and then to turn that into a percentage.

These are date fields, if that's important.

Would anyone be able to assist? Thanks!!!

Re: set analysis with a subtraction

hic — Tue, 19 Feb 2013 20:15:52 GMT

You do not need Set analysis for this.

First, what is your dimension? Let's assume that there is some sort of Transaction that has a date_received. Then you could use TransactionID as dimension.

Next is the expression. You could directly use apel_ltr_snt_dt - date_received as expression. But that just works if there is only one apel_ltr_snt_dt and only one date_received per TransactionID. If there are several, you need to specify which aggregation function to use, e.g. Avg(apel_ltr_snt_dt - date_received) or Avg(apel_ltr_snt_dt) - Avg(date_received).

Percentage? It can easily be done, but compared to what?

HIC

Re: set analysis with a subtraction

Thu, 21 Feb 2013 01:45:06 GMT

basically, each apel_id has a received date, and a sent date.

the received date is when my company receives the letter from a member, and the sent date is when we sent out a reply to the member.

so, I would need to show a chart, with the Y axis as percentages, from 0% to 100%, in 10% increments.

then I need to show (by apel_id) the percentage of letters that were sent out within 30 days or receiving them.

so, if the "apel_ler_snt_date" - "date_received" <=30, then it's good. else, it counts against us.

if, say, we had 100 apel_id's in October, and all sent dates were within 30 days of the received date, October should show a bar up to 100%.

Does that make sense?

Re: set analysis with a subtraction

hic — Thu, 21 Feb 2013 06:59:33 GMT

I would, already in the script, create a couple of new fields:

apel_ltr_snt_dt - date_received as Delay,

class(apel_ltr_snt_dt - date, 10 , 'Delay') as DelayClass

Use one of these as dimension in a chart.

The expression should be

Count(distinct apel_id)/Count(distinct total apel_id)

Finally, format the expression as percentage (Properties - Number - Show in percent)

HIC

Re: set analysis with a subtraction

Thu, 21 Feb 2013 18:45:10 GMT

Worked like a charm! Thanks!