Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a Pivot table with 3 dimensions and 1 measure
In the measure (quantity)I have this definition
if(W1TEXT = 'Beginstock', sum({ $ <KeyDatum = {$(vMindate)} >}W1HOEV), if(W1TEXT = 'Eindstock', sum({ $ <KeyDatum = {$(vMaxdate)} >}W1HOEV),sum(W1HOEV))).
The values in the table are correct.
Only the total is the value of sum(W1HOEV) (Quantity Temp)
Who can help me with the correct Total?
Regards,
Kris
One option
RangeSum(
Sum({$<KeyDatum = {$(vMindate)}, W1TEXT *= {'Beginstock'}>}W1HOEV),
Sum({$<KeyDatum = {$(vMaxdate)}, W1TEXT *= {'Eindstock'}>}W1HOEV),
Sum({$<W1TEXT -= {'Beginstock', 'Eindstock'}>}W1HOEV))
and other one is to
Sum(Aggr(if(W1TEXT = 'Beginstock', sum({ $ <KeyDatum = {$(vMindate)} >}W1HOEV), if(W1TEXT = 'Eindstock', sum({ $ <KeyDatum = {$(vMaxdate)} >}W1HOEV),sum(W1HOEV))), StationAlles, W1OMAR, W1TEXT, _CalYear, _CalMonth, _CalDay))
Hi,
The pivot table is showing individual values per dimension. The most likely reason you are seeing these is that instances are duplicated (not unique) and is therefore summing up to a higher value.
Hi,
thanks for your quick support.
And how can I solve this issue?
The total mode is grayed out So I can't sit this to Sum of rows.
Can I do some thing in the expression?
can you share your app?
Here is the app.
The total should be correct for every view.
If I only see 3 dimensions it should also be corect if possible
One option
RangeSum(
Sum({$<KeyDatum = {$(vMindate)}, W1TEXT *= {'Beginstock'}>}W1HOEV),
Sum({$<KeyDatum = {$(vMaxdate)}, W1TEXT *= {'Eindstock'}>}W1HOEV),
Sum({$<W1TEXT -= {'Beginstock', 'Eindstock'}>}W1HOEV))
and other one is to
Sum(Aggr(if(W1TEXT = 'Beginstock', sum({ $ <KeyDatum = {$(vMindate)} >}W1HOEV), if(W1TEXT = 'Eindstock', sum({ $ <KeyDatum = {$(vMaxdate)} >}W1HOEV),sum(W1HOEV))), StationAlles, W1OMAR, W1TEXT, _CalYear, _CalMonth, _CalDay))
Thanks Sunny,
These both seem to work.
Which should I take?
regards,
Kris
I would suggest using the 1st option unless you see it causing problems...
Ok, Thanks for the quick help.