Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I decided to ask the community for this one, i am all out of ideas:
I have three dimension tables and a fact. All dimensions are dates, and the fact has three date columns and one ID.
First dimension is the EnteredDate, the second is SentDate and the third is ReceivedDate.
How can I achieve a graph with three different metrics(expressions) and show in time the count of ID's for each dimension?
Entered:
ID Month
1 jan
2 jan
3 feb
4 feb
5 mar
Sent:
ID Month
1 jan
2 feb
4 feb
5 mar
Received:
ID Month
1 mar
4 mar
Desired result:
Month Entered Sent Received
Jan 2 1 0
Feb 2 2 0
Mar 1 1 2
Thank you!
Hi,
You can try this:
Load
Id,
DateEntered as Date,
'Entered' as Flag
From T1;
Concatenate
Load
Id,
DateSent as Date
'Sent' as Flag
From T1;
Concatenate
Load
Id,
DateReceived as Date
'Received' as Flag
From T1;
Hope this will help
If you can live with it, I would recommend the following table structure:
LOAD * Inline [
Month,ID,Status
jan,1,Entered
...
...
jan,1,Sent
...
...
mar,1,Received
...
...
];
Where ... represents data I unfortunately don't have time to type in. So, "Status" is a new field which tells you which table the data originates from.
Then you can have Month as your Dimension, and your straight table expressions would be
count({$<Status={"Entered"}>} ID)
count({$<Status={"Sent"}>} ID)
count({$<Status={"Received"}>} ID)
Hopefully that made sense
hi check the attach example
hi all,
thank you for the help, but i am not able to load the data in this way.
I have an operational database (no aggregates available) with one big table that has:
ID .... DateEntered DateSent DateReceived ...
I created, based on this, a start schema with the ID as a fact (and other columns) and with KeyDates for each date dimensions. See attached screenshot. I want to be able to move from a month level to a quarter or year, etc. so i need those tables as they are now.
The only way i can think of is creating a separateisland, but i'm not sure how to do that in my case, because i think i need a separate calendar table that can join with duplicates of the three time dimensions. Or not?!!?
Thank you!
Hi,
You can try this:
Load
Id,
DateEntered as Date,
'Entered' as Flag
From T1;
Concatenate
Load
Id,
DateSent as Date
'Sent' as Flag
From T1;
Concatenate
Load
Id,
DateReceived as Date
'Received' as Flag
From T1;
Hope this will help
Hi,
Please look at the attached file. It solves your problem.
Thanks,
Niranjan M
thank you! i forgot the power of Concatenation