Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have a big issue. I have two tables.
Table A where I have the user ID (user ID is the key field) and the ID name.
And table B which provides information on tickets opened and in this table i have the field of:
i) the user who created the ticket (Requestor)
ii) the user who closed the ticket(user ID).
Currently the key field is connected with the user who solved the ticket.
Right now I need to build a bar chart where my Dimension is the 'ID name' and the number of tickets created by the users for the last 5 days.
My expression is as follows:
=Count({<Date={'>=$(=Date(Max(Date)-5))<=$(=Date(Max(Date)))'}>} Requestor)
However, Qlikview is showing the good dimension but is counting the user ID(the user who closed the ticket).
Could you please help.
Many Thanks,
Hasvine
you can create one more column in A ,
[user ID] ,
[user ID] as Requestor
so that your both table will link with two fields
then use same expression.
Thanks
BKC
Hi,
Try this.
Users:
LOAD * INLINE [
User ID, ID Name
1, a
2, b
3, c
4, d
5, e
];
Tbl:
LOAD * INLINE [
User ID, Ticket, Source
1, 32, Request
2, 333, Request
3, 345, Request
];
Concatenate
LOAD * INLINE [
User ID, Ticket, Source
4, 32, Closed
3, 333, Closed
5, 345, Closed
];
You expression should read : =Count({<Date={'>=$(=Date(Max(Date)-5))<=$(=Date(Max(Date)))'}, Source = {'Request'}>} [User ID])
Hi Hasvine, what QV is doing in your model is:
- You select an [User ID], so the model is filtered to that [User ID], which filters the tickets closed by that user.
- When you count the requestor, QV is counting how many tickets closed by that [User ID] was opened by someone (can be another user)
Maybe this expression can help you:
=Count({<Date={'>=$(=Date(Max(Date)-5))<=$(=Date(Max(Date)))'}, Requestor=[User ID], [User ID]>} Requestor)
Not tested, if you can upload a sample it will be nice to make some tests and give a tested answer.
Hope this helps!
Hi Chandel,
Thank you its works now but however I do not get the correct answer when i use ID name as the Dimension. When I use the Requestor the answer is correct. But the requestor are numbers and these numbers are linked to name in the database.
Do you have an idea of how i can solve this issue please?
Have a look at this posting Canonical Date
It applies to multiple date fields, but you can easily apply the same concepts to your user IDs.
if can share your sample app after reducing the data . That would be helpful to ans .
Thanks
BKC
I managed to do it.I created an excel sheet and with the requestors id and requestor name and i uploaded the file and finally it works.I know its not the best way to do it but I guess its the easiest 😄
Thank you all for your help.
We had a similar problem, and translated to your case, the solution was to split the user table into two separate tables with different field names (RequestorID, RequestorName) and (CloserID, CloserName) in the load script.
This article might be helpful as well:
Hi Robert,
Thanks for your help. I manage to solve the issue.
Thanks,
Hasvine