I have this data set where I have several entries containing the date in the DD/MM/YYYY format. Since I don't want it to be displayed that way, I used
MonthName("Date") as Date
in the data load editor to load the data. There are supposed to be 11 data points in that data set.
However, when I do that I lose 1 data point! There are two rows in my excel file that are exactly the same bar one field, and those 2 got combined. I do not want that to happen, so what do I have to do?
I know that they got combined in some way because when I applied the Count(Date) to a certain visualisation that I had, it gave me a value of 10 instead of 11.
Solved! Go to Solution.
The above tables have multiple field that are identical, that generates a synthetic table. My recommendation is that you eliminate synthetic tables, as this will give you a more predictable data model. There are many ways to approach this problem, and it all depends on what you are trying to do and how your data relates.
The key thing is to only have one common field in the linked tables. Simply this means you tables need to be renamed in some way.
It is work related data, so I am afraid that I cannot share the data here, but yes they were 2 different days. The entire data set that I had contains data from a particular month and it just happens that those 2 data points got combined.
To provide more context, the dataset is some sort an error report and the two points that got combined were errors regarding the same system, and thus they are almost identical in terms of content apart from the day and the report ID. If more information is really needed, I would try to see how I "clean" the data to hide the sensitive information
no worries about the data, is just that it would provide a more cleaner picture
since your main issue is with the date format do not use monthname, instead use date(fielddate,'MMM YYYY') that way it will not group all of the different dates into a same value
That does solve the problem of the data fields combining, but I want to collate all data points in February and present them in a monthly view, if I do it in that manner, they still show up as separate days when thrown into my visualisation. That is, I want it to show as a stacked bar of all the errors in the month of February and for the value of the stacked bar to be 11 instead of 10.
Initially I was using this
MonthName("Date") as “Date”,
"Status / Remarks",
you keep throwing curve balls at me , how is you count or sum being done ? based on the date ?
try creating one field with the month name and another with the date format, and use the monthname one as the dimension in you chart
another option is to create a calculated dimension in the chart I have used something like this before date(MonthStart(datefield),'MMM YYYY')
My count is done based on the Name of the system, so I have a stacked bar chart, with each colour representing a different system. Therefore my 2 dimensions are date and system, while my dimension is count(system).
I will give what you have suggested a try later and provide an update then.
The sample above can impossible explain that two rows get merged. There are many ways in which you might "accidentally" merge data. Unfortunately, I am not able to take time and list them all to you, so if you could provide a complete sample script it would make it easier to find the root cause.
Please provide the complete script including any joins or concatenations you do that involves this table.