How to use two fields as dimension

[wXavier91]

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

[aslam24196]

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

 Hope it helps!

[wXavier91]

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]

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. 🙂