Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
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
Thanks a lot, now it's working correctly.
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