I have a pivot table which has to sum up some data over a period of time.
My calculation field look like:
${Mode} = ‘YTD’,
periodToDateSumOverTime(
sumIf(
{kpi_value},
{source_table} = ‘my_table’
AND {exercise_type} = ‘ACTUAL’
AND extract(‘MM’, {kpi_date}) >= extract(‘MM’, ${BeginningSelectedTimeframe})
AND extract(‘MM’, {kpi_date}) <= extract(‘MM’, ${EndSelectedTimeframe})
AND extract(‘YYYY’, {kpi_date}) = extract(‘YYYY’, ${EndSelectedTimeframe})
),
{kpi_date}, YEAR)
But somehow in the pivot table it adds up correctly for 4 or 5 values than it’s wrong. I added it to a regular table and the calculation shows up correctly.
It adds up correctly even when there is no value for that date but than for Jan 9 in this case it’s wrong. This kind of mismatch is all around the table.
Hi @sandor93 , welcome to the QS Community!
It will be useful to have also a sample of the raw data you used!
Can you share anonymized data to try to replicate you case?
Hi @andrepgn,
Thanks for your replay, unfortunately I cannot share sample data.
I have 5 attributes for the rows that are related, ‘brand → model → series → model_series ->model_code’ and they are also added as a filter from the controls.
I think it could be more an error due to the management of the dates, likely inclusion of initial hours of a specific day or something like that, but it’s difficult to replicate the issue without some more detail.
Can you at least provide the structure of your original dataset (like fields name) and a screenshot or info on how Pivot is configured and also the params you are using?
I managed to narrow down the isse. If I remove model_series and model_code than it calculated correctly or if I expand e_series (for e.g F44) than it is also correct. Just in this collapsed state the calcualation is wrong.
Hi @sandor93,
Following up here as it’s been awhile since last communication took place on this thread; are you still working on this issue or were you able to find a work around in the interim?
If we do not hear back within the next 3 business days, I’ll close out this topic.
