4 Replies Latest reply: Nov 8, 2012 8:40 AM by Lorenzo Cavicchioli

# selection with expression not working properly

Hi,

I have used a very simple expression evaluating the difference between two dates: [Completed Time]-[Created Time];

when I click on a value in the "Number of days" column, though, the selction is not working properly.

In other words, I would expect that if I click on, e.g., o days, all other risults would disapper and selection propagate to other tables/charts, for example in the crono table which I put just for a check. The selection is propagation but I see also other results...

Sorry for poor english and poor explanation; the attached file should be a much better explanation and easy-to-understand as I left only what strictly necessary to understand the problem.

Lorenzo

p.s. in attachment qlikview file and excel data file

• ###### Re: selection with expression not working properly

It's best to create a field in your script to classify your number of days, thus allowing also easy selection on this classification:

Generale:

Year(date([Created Time])) as AnnoCreatedTime,

Month(date([Created Time])) as MeseCreatedTime,

Year(date([Completed Time])) as AnnoCompletedTime,

Month(date([Completed Time])) as MeseCompletedTime,

if (([Completed Time]-[Created Time])='0','0 days',

if (([Completed Time]-[Created Time])='1','1 day',

if (([Completed Time]-[Created Time])='2','2 days',

if (([Completed Time]-[Created Time])='3','3 days',

if (([Completed Time]-[Created Time])='4','4 days',

if (([Completed Time]-[Created Time])='5','5 days',

if (([Completed Time]-[Created Time])='6','6 days',

if (([Completed Time]-[Created Time])>'6','More than 6 days'))))))))

as [Number of days];

[Created Time],

[Completed Time]

FROM

data.xlsx

(ooxml, embedded labels, table is Foglio1);

• ###### Re: selection with expression not working properly

Thanks a lot, now it's working correctly.

• ###### Re: selection with expression not working properly

Hi lorecavi

The calculated dimension 'Number of Days' is [Created Date] - [Completed Date] where as the 'Days' in 'Crono' table is calculated using NetWorkDays function. They are different and thats why the selection is not working as your are expecting.

I think you are interested in only work days, so I'd recommend using NetWorkDays in the calculated dimension.

E.g.

Replace all ([Completed Time]-[Created Time]) with (NetWorkDays ([Completed Time],[Created Time]))

=if (([Completed Time]-[Created Time])='0','0 days',

if (([Completed Time]-[Created Time])='1','1 day',

if (([Completed Time]-[Created Time])='2','2 days',

if (([Completed Time]-[Created Time])='3','3 days',

if (([Completed Time]-[Created Time])='4','4 days',

if (([Completed Time]-[Created Time])='5','5 days',

if (([Completed Time]-[Created Time])='6','6 days',

if (([Completed Time]-[Created Time])>'6','More than 6 days'))))))))

I hope this helps..

Regards

MultiView

• ###### Re: selection with expression not working properly

Hi Multi View,

you're right about me forgetting NetWorkDays, but that an't the point, because the selection is not working not only in the crono table but in the original table itself. Anyway swuehl gave the correct answers.

Thanks,

lorecavi