topic Re: Several tests on same field in set analysis in QlikView

Several tests on same field in set analysis

Anonymous — Fri, 22 Mar 2013 21:47:10 GMT

Hi!

I have this set analysis that works fine and select records with FASTTRACK_SENT_NUM within the last 12 months:

count ({$<FASTTRACK_SENT_NUM={'>$(=NUM(AddMonths(makedate($(vYear),$(vMonth),1), -12)))<$(=NUM(AddMonths(makedate($(vYear),$(vMonth),1), 0)))'}>}COUNTER)

My problem is that I need another test on the field FASTTRACK_SENT_NUM in this set analysis.

In addition to the test above I also need to select records where FASTTRACK_SENT_NUM <= FASTTRACK_DUE_NUM.

Both FASTTRACK_SENT_NUM and FASTTRACK_DUE_NUM are date-fields converted to numeric format in the load script and they have exactly the same format.

How will the set analysis look like after including this extra test?

Best regards

Torunn

Re: Several tests on same field in set analysis

jagan — Sat, 23 Mar 2013 07:27:32 GMT

Hi,

Try this

Sum(If(FASTTRACK_SENT_NUM <= FASTTRACK_DUE_NUM, count ({$<FASTTRACK_SENT_NUM={'>$(=NUM(AddMonths(makedate($(vYear),$(vMonth),1), -12)))<$(=NUM(AddMonths(makedate($(vYear),$(vMonth),1), 0)))'}>} COUNTER) ))

Regards,

Jagan.

Re: Several tests on same field in set analysis

Anonymous — Sat, 23 Mar 2013 10:48:40 GMT

Hi Jagan!

First of all thank you for responding on my problem!

I have tried your suggestion, but I don't get this to work. I do only get ' - ' as the result in the column.

I don't know if you have som other suggestions that I can try?

Best regards

Torunn

Re: Several tests on same field in set analysis

Gysbert_Wassenaar — Sat, 23 Mar 2013 10:53:53 GMT

Try:

count ({$<FASTTRACK_SENT_NUM= {"=FASTTRACK_SENT_NUM<=FASTTRACK_DUE_NUM"},

FASTTRACK_SENT_NUM={'>$(=NUM(AddMonths(makedate($(vYear),$(vMonth),1), -12)))<$(=NUM(AddMonths(makedate($(vYear),$(vMonth),1), 0)))'}>}COUNTER)

If both date fields are in the same table, you could add a field to flag if FASTTRACK_SENT_NUM <= FASTTRACK_DUE_NUM:

if(FASTTRACK_SENT_NUM <= FASTTRACK_DUE_NUM, 1, 0) as fFTSentBeforeDue. Then you can simply use the new field in your set analysis expression.

Can you share a sample document? You can upload files by clicking on the Use advanced editor link after you click on the Reply button.

edit: changed the first expression so it returns a result. But better use the flag field

Re: Several tests on same field in set analysis

Anonymous — Sat, 23 Mar 2013 11:10:13 GMT

Hi Gysbert!

I have tried your suggestion and I do now get ' 0 ' as the result of the expression. I know there are records that should match the expression. Do you have any more ideas that I could try?

Best regards

Torunn

Re: Several tests on same field in set analysis

Anonymous — Sat, 23 Mar 2013 12:02:09 GMT

Hei Gysbert!

I could not attach the total qvw because this is a part of a big model, but I have made a small qvw with the fields you need.

My need is to count number of records (%Quote_id) with "fast-track-sent" last month and check how many that was sent within "fast-track-due" that month.

You can see the table I need in the qvw. Note that the source-data includes records for several months and that is why the expression needs several tests on the field "fast-track-sent".

I hope the attached file can help you.

Best regards

Torunn

Re: Several tests on same field in set analysis

Gysbert_Wassenaar — Sat, 23 Mar 2013 12:44:52 GMT

You're best off with calculating a flag field in the script. See attached qvw

Re: Several tests on same field in set analysis

Anonymous — Sat, 23 Mar 2013 12:53:04 GMT

Thank's a lot Gysbert!

I shall do as you suggest and calculate a flag field in the script.

Best regards

Torunn