What is wrong with the logic of this date query

[Anonymous]

Hi all,

So I have the following in the back-end of my app:

if	(DAYS <=30, 'Active within 30 Days',
if	(DAYS <=60, 'Active within 60 Days',
if	(DAYS <=90,	'Active within 90 Days'))) as Active_Days

so basically, when I click on "Active within 30 Days", the results returned shows any DAYS smaller than 30.

But when I click on "Active within 60 days", it shows me DAYS BETWEEN 30 and 60 days, and not everything up until 60 days.

The same goes for the last selection.

What is wrong with the logic of this code?

Thank you,

Stefan

[sunny_talwar]

I like that Joe‌

Another way this might be handled:

If(DAYS <=30, Dual('Active within 30 Days', 1),

If(DAYS <=60, Dual('Active within 60 Days', 2),

If(DAYS <=90, Dual('Active within 90 Days', 3) ))) as Active_Days

And then within the expression:

{<Active_Days = {"=Num(Active_Days) <= Num(Max(Active_Days))"}>}

Have not tested it out, but I believe it should work.

Best,

Sunny

[Anonymous]

The problem comes in when I want to calculate averages.

If we look at the full load:

if (DAYS > 0  and DAYS <= 30, '<= 30 Days',

if (DAYS > 30 and DAYS <= 60, '>30 <= 60 Days',

if (DAYS > 60 and DAYS <= 90, '>60 <=90 Days','>90 Days'))) as ACTIVE_DAYS,

if (DAYS > 0   and DAYS <= 90,  'Average Spend within 3 months',

if (DAYS > 90  and DAYS <= 180, 'Average Spend between 3 and 6 months',

if (DAYS > 180 and DAYS <= 365, 'Average Spend between 6 and 12 months'))) as AVERAGE_SPEND,

TRANSACTION_AMOUNT

Say for example I wish to calculate the average TRANSACTION_AMOUNT for the first 3 months,

it will be the sum of TRANSACTION_AMOUNT / 3.

If I were to calculate the average for the first 6 months, it will be TRANSACTION_AMOUNT / 6.

Now, if the user wish to calculate the average amount for the first 6 months, he will need to select both

'Average Spend within 3 months' and 'Average Spend between 3 and 6 months' ?

I'm not too sure this is the correct way to do it.

[sunny_talwar]

If you share some sample data, we might be able to give a solution which might work for you. Can you provide few lines of data which is representative of your true data?

Best,

Sunny

[sunny_talwar · Accepted solution]

I created some dummy data to demonstrate the Dual solution.

Script:

Table:

LOAD DAYS,

  [TRANSACTIONAL_VALUE],

  Dim,

  If(DAYS <=30, Dual('Active within 30 Days', 1),

  If(DAYS <=60, Dual('Active within 60 Days', 2),

  If(DAYS <=90, Dual('Active within 90 Days', 3) ))) as Active_Days;

LOAD Floor(Rand() * 90) + 1 as DAYS,

  (Rand() * 100000) as [TRANSACTIONAL_VALUE],

  Floor(Rand() * 10) + 1 as Dim

AutoGenerate 100;

When you have selected Active within 90 Days you will see all three buckets:

Similarly Active within 60 Days will show the first two buckets:

All this is done using the following set analysis:

{<Active_Days = {"$(='<=' & Max(Active_Days))"}>}

Since Active_Days have a numeric value attach to it, I can say within set analysis to select everything less than max. Active within 60 Days is equal to 2 so it selects Active within 60 Days and Active within 30 Days.

Also attaching the qvw for your reference.

I hope this is what you are looking for.

Best,

Sunny

[Anonymous]

Hi Sunny,

This method worked perfectly

I can do the same now to my average spent within a certain amount of days as well.

Your help is much appreciated!

Cheers,

Stefan

[sunny_talwar]

Stefan -

I am glad that this solution makes more sense.

There are always (almost always) many options to solve a problem and each of the solution have there own constraints. It becomes difficult to propose the best solution without knowing the constraints.

But in the end I am glad we were able to help you out.

Best,

Sunny