Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wXavier91
Contributor
Contributor

How to use two fields as dimension

Hi, community. 

I have a database which contains three fields: request_id, request_opening_date, request_fulfillment_date.

I need to show in the same bar chart the number of requests opened and closed by month (two separate bars per month).

For example: if a request was opened in January and fulfilled in February it would count +1 in the "January opened requests" and +1 in the "February closed requests". If a request was opened and closed in April it would count +1 in the "April opened requests" and +1 in the "April closed requests".

The problem is that, to count the openings I use request_opening_date as dimension, to count the fulfilled I use request_fulfillment_date as dimension and I can't figure out how to combine both in a single chart.

 

I hope you can help me.

Regards.

Welton

Labels (2)
3 Replies
aslam24196
Contributor III
Contributor III

There could be better approaches but here goes:

Test:
Load * INline [
ID, Opened, Closed
1, Jan, Feb
2, Jan, Apr
3, Mar, Mar
4, Feb, Apr
5, Apr, Apr
6, Mar, Apr
];

Open:
Load ID,Opened as [Opened/Closed Date],'Opening Date' as Flag
Resident Test;

Concatenate
Load ID,Closed as [Opened/Closed Date],'Closing Date' as Flag
Resident Test;

 

Then in Chart using [Opened/Closed Date] as Dimension,

Count({<Flag={'Opening Date'}>}[Opened/Closed Date]) as Measure 1

Count({<Flag={'Closing Date'}>}[Opened/Closed Date]) as Measure 2

I got the following Chart

clipboard_image_0.png

 Hope it helps!

wXavier91
Contributor
Contributor
Author

Thanks for the idea, Aslam.

It works as I asked! But in my case I have another issue.

This chart is not the only one in my dashboard. Now I need to link this table with my other tables so it can be filtered according to the user selections.

I can't find this new created table in the Data Manager section, where I usually make the associations.

Do you guys know how do I link a table created via Data Load Editor with an already existing one?

 

Regards.

aslam24196
Contributor III
Contributor III

Try renaming the key field exactly as it was in the existing table. It automatically associates. But if you want to get into script then you can always unlock the script in load editor and transform and do the magic. 🙂