Hi,
I’m calculating difference in dates for 2 dates by 2 datasets and not sure why QS is returning 2 different values for exactly same underlying data. These 2 tables are built based on different datasets but the fields and underlying data are exactly the same. Column NetWorkDays is aggregated as average and the formula i’m following is:
This is very strange indeed, the average should give you 2, since the netWorkDays returns an integer I am not sure how are you getting those results.
I made a small table with the data from your SS and I am getting the correct results. I even added decimals to the output of netWorkDays to make sure this was an integer. Also created the same line graph and I get the average in the correct place.
Can you check if you set the netWorkDays to show the format with decimals if it is showing something different, like a calculation from the dataset and not the result from the calculation?
I cannot imagine anything else that will make this kind of calculations show all different values.
Hi @andres007 thank you for checking on this. I’ve checked and the filed is set as number, and used the decimal places to check as well:
As you said i expect total as 2, no idea what’s going on here. I believe this might be related to my dataset but since i can see the underlying data is matching yours then i’m confused here.
My guess is that there are multiple rows that have the same combination of date 1 and date 2. You don’t see them in your visual because you’re grouping by date 1 and date 2. Add all the remaining fields of your dataset to your visual and you’ll see where the duplicates are coming from.
Hi @andres007 , @David_Wong I’ve figured this out. I needed to adjust the aggregation method at column level from Default to Average. Honestly I still don’t know what’s the calculation for Default method.
@andres007 however when i’m trying to show the total within line chart visual > it’s giving me the value which isn’t matching the one with table (it’s matching total in table when aggregating as “default”. I’m using NetWorkDays within chart value as “Average”. As x axis i’m utilizing Date 2 as month. Honestly I don’t know why QS interpretes it in different way since these underlying values are integers.
Here’s the same table with “Default” aggregation for NetWorkDays total:
Hovever when I’ve tested the same data grouped by Date 2, then the average is always 5.1 and it doesn’t adjust when ameneded total aggregation method from default to average. So i’m thinking what’s the logic behind default total calculation here:
I think this is related to your aggregation on Date1 and Date2, it is possible that you have more than one entry for the days, with different times? Then the average is not the same as since the agregation of the line graph is only on Date2.
So basically, if you leave the same aggregation on the table and the line graph, with the value as average, then you should see the same results even if the total is Average or Default.
The Average in the value of the column is there to calculate the Average of the rows that are grouped by, but if you average the sum of some averages you will not get the same result as the average of all the rows, which is what the Totals is showing when you select Average.
@andres007 there are some cases when i have multiple entries for date 2 under same item id but im looking at the oldest one by minOver() function. That’s why i always have 1 item with exactly 1 entry for date1/2.
Sorry for the confusion but for each date there is 1 unique item assigned, which i skipped on the screenshots.
So basically as you said aggregation method shouldn’t be the matter here since there are no multiple values here (or i’m missing something).
When tested the very same data it’s working in the way i’m looking for but since i’m utilizing SQL database i cannot go for such workarounds.
Hi, without having access to the underlying data and all the calculations you are using to come up to the table you are showing it is hard to come up with a conclussive answer since the different funcions can intereact in different ways, as you have found yourself.
If you are using the minOver() funciton you might want to take a look at the PRE_AGG part.
Or just try the min() function if you are not using partitions.
Thanks @andres007 will try different aggregation method then, i’m currently using PREAGG.
I retrieved all the underlying data so indeed QS is considering all to the average, but i need to have this highlighted one to be countend just as only 1 record (under same id):
Hi @andres007 I’m just trying to understand what’s behind default total aggregation method and why it’s returning value which doesn’t have anything in common with underlying data. I’ve tested it without minOver() as well, but that default average is always different than expected value:
Scenario 1 (wrong output)
Scenario 2 (correct output):
Last part is when aggregating by month - why changing total from default to average isn’t adjusting data as in the table?
I’ve tested LAC-A aggregation method for Date 1/Date 2 and I’m able to get the default values as expected:
However when trying to aggregate by by month i’m getting this error, because Id field is missing in wells (which i don’t need because i want to show total on the graph):
I can use such pivot but i’d prefer to have it as chart:
I believe this all obstacles could be somehow connected with my SQL but there are just 2 simple joins, so there shouldn’t be any issues. What’s more output raw data is fine, so it must be connected with some QS limitations.
I’ve just found a definition for default within documentation but isn’t working for my case:
I see you have advanced since the last I read you reply. I think we are getting closer to the answer.
Is it possible for you to send me a csv with the data as it is on the database so I can try myself with the raw data? Off course only the ones relevant to the issue, id/d1/d2. If the ID is not a UUID or something random please mask / encode it. That will be very helpful for me to try to get into the details, specially when LAC is involved.
It has been some time since we have heard from you but would still like to help you find a solution. Was @David_Wong or @andres007’s response above helpful and if so could you mark their comment as a solution to help the community?
