Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

Thanks a lot in advance,

Lorenzo

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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:

Load *,

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];

LOAD [Request ID],

     [Created Time],

     [Completed Time]

    

    

FROM

data.xlsx

(ooxml, embedded labels, table is Foglio1);

View solution in original post

4 Replies
swuehl
MVP
MVP

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:

Load *,

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];

LOAD [Request ID],

     [Created Time],

     [Completed Time]

    

    

FROM

data.xlsx

(ooxml, embedded labels, table is Foglio1);

Anonymous
Not applicable
Author

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

Not applicable
Author

Thanks a lot, now it's working correctly.

Not applicable
Author

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