Solved: What is wrong with the logic of this date query - Qlik Community

Anonymous · ‎2015-07-20

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 · ‎2015-07-21

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

View solution in original post

sunny_talwar · ‎2015-07-20

If statement goes in the order

When you have this:

if(DAYS <=30, 'Active within 30 Days',

if(DAYS <=60, 'Active within 60 Days',

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

If actually means

If(DAYS <= 30, ...

If(DAYS <= 60 and DAYS >= 30, ...

and so on...

sunny_talwar · ‎2015-07-20

Try this may be:

Table

LOAD DAYS,

otherFields,

'Active within 30 Days' as Active_Days

Resident source

Where DAYS <= 30;

Concatenate (Table)

LOAD DAYS,

otherFields,

'Active within 60 Days' as Active_Days

Resident source

Where DAYS <= 60;

Concatenate (Table)

LOAD DAYS,

otherFields,

'Active within 90 Days' as Active_Days

Resident source

Where DAYS <= 90;

Report Inappropriate Content · ‎2015-07-20

if

(DAYS <=30, 'Active within 30 Days',

if

(DAYS <=60 and Days>=0, 'Active within 60 Days',

if

(DAYS <=90 and Days>=0,

'Active within 90 Days'))) as Active_Days

Anonymous · ‎2015-07-20

Hi Sunindia,

This worked perfectly thank you

However, when aggregating values on this table, I might end up with values 3x the original amount.

We decided on grouping our values differently

sunny_talwar · ‎2015-07-20

Awesome,

I am glad I was able to help.

Best,

Sunny

Report Inappropriate Content · ‎2015-07-20

just FYI take care with this, you are multiplying up your table rows now for each bucket the day falls into, which will affect other expressions built in your application that use those fields from the table.

Not sure what your model looks like, this may not be a problem for you, but thought it worth pointing out.

Joe

Anonymous · ‎2015-07-20

Hi Joe,

I also found that this will cause problems when summing values on this table Im working with.

We decided on using the following grouping:

Active <30 Days

Active >=30 and <60 Days

Active >=60 and <90 Days

So if the user want to see days falling under 60, they must highlight the first two options in order to see the results.

Report Inappropriate Content · ‎2015-07-20

Hi Stefan,

if you really did want a grouping in this way without inflating your base table, then you could add this as a side dimension table instead just containing a days key field and the the possible buckets each day falls into.

e.g

Days_grouping:

Load Inline [

Days_Key, Active_Days

1,Active within 30 days

1,Active within 60 days

1,Active within 90 days

.

31,Active within 60 days

31, Active within 90 days

.

61, Active within 90 days

];

rather than an inline, use your base data to build the table but hopefully you get the idea. you can select the active value you want but won't inflate your fact data.

Hope that helps

Joe

sunny_talwar · ‎2015-07-20

I doubt this will work ranga‌, just because the way if statement works. As soon as DAYS will be less than 30 for a particular row, it will be assigned Active within 30 days and rest of the values won't get any chance.