Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table with 4 dimensions
Month as Column
Account
Category
Department
Expression is:
Sum({$<Year={$(=(Year)-0)}, Month=, ExclCo={"0"}, FSCSDSG={"A"}, Acct={">=4110<=9999"}, ActiveDate={"<=$(=Date(RANGEMIN(AddMonths(MAX(ActiveDate),0,1))))"}>}NetAmt) /
Sum(TOTAL{$<Year={$(=(Year)-0)}, Month=, ExclCo={"0"}, FSCSDSG={"Q"}, Acct={">=4000<=4029"}, ActiveDate={"<=$(=Date(RANGEMIN(AddMonths(MAX(ActiveDate),0,1))))"}>}NetAmt)
This gives me incorrect totals.
I tried
Aggr(Sum({$<Year={$(=(Year)-0)}, Month=, ExclCo={"0"}, FSCSDSG={"A"}, Acct={">=4110<=9999"}, ActiveDate={"<=$(=Date(RANGEMIN(AddMonths(MAX(ActiveDate),0,1))))"}>}NetAmt) /
Sum(TOTAL{$<Year={$(=(Year)-0)}, Month=, ExclCo={"0"}, FSCSDSG={"Q"}, Acct={">=4000<=4029"}, ActiveDate={"<=$(=Date(RANGEMIN(AddMonths(MAX(ActiveDate),0,1))))"}>}NetAmt),Category,Dept,Account)
Doesn;t work either.
If I remove all the dimensions and just keep Month as Dimension, I get correct answer.
Any help is appreciated. @sunny_talwar
Would you be able to share a sample where we can see the issue?
Sorry, app size is close to 800 MB. Even if I scramble the data..Plus it is finance data
This is for a complete view
Sum({$<Year={$(=(Year)-0)}, Month=, ExclCo={"0"}, FSCSDSG={"A"}, Acct={">=4110<=9999"}, ActiveDate={"<=$(=Date(RANGEMIN(AddMonths(MAX(ActiveDate),0,1))))"}>}NetAmt)
If you can help me with expression?
I just tried below- This is for individual values ( there is a button assigned to view Total values in $ and then Values per scan)-Below is for Per scan, above is for complete view.
Complete view is correct, but per scan gives incorrect scans
Sum({< ExclCo={"0"}, FSCSDSG={"A"}, Acct={">=4110<=9999"} ,Month=, ActiveDate=, ActiveDate={">=$(=Num(YearStart(Max(ActiveDate))))<=$(=Max(ActiveDate))"}>}NetAmt) /
aggr(sum( total{$<FSCSDSG={"Q"}, Acct={">=4000<=4029"} >}NetAmt),Category,DeptDescr,ACCTDESC)
This gives me individual month only, but I wanted to see, if I select Apr month, I should be able to see Jan, Feb, Mar & Apr month.
When I adjust it to show previous months, totals go wrong. It only shows correct value for Individual months.
I'm quite sure, it has something to do with expression/ignoring the fields selected.
Thanks.
Main issue is, a month selection gives same total for all other months.
Ex: Apr-2020 is 6354
Jan, Feb, Mar also counts total as 6354