Several tests on same field in set analysis

[Anonymous]

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

[jagan]

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.

[Anonymous]

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

[Gysbert_Wassenaar]

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

[Anonymous]

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

[Anonymous]

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

[Gysbert_Wassenaar · Accepted solution]

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

[Anonymous]

Thank's a lot Gysbert!

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

Best regards

Torunn