Can i use P() or E() with aggr()

[inoruoya]

Dear,

i need to use this expression on pivot table and have on dimension that "Year"

so possible that expression return on chart is all of account possible.

but i need to return possible account on that year

Expression ::

sum({<

     Month={$(=month(max(DateKey)))}

     ,Account= P({<Month={$(=month(max(DateKey)))},C={X},D={X}>} Account)

     >} AmountDMBTR_BS)

remark : I have data in as of month (Accum as of month)

thanks,

[micheledenardi]

Hi Anya,

apply these changes and then let me know:

change: C={X},D={X} to C={'X'},D={'X'}

change: {$(=month(max(DateKey)))},C={'X'},D={'X'}>} to {"$(=month(max(DateKey)))"},C={'X'},D={'X'}>}

If these changes doesn't work we need some example data.

[tresesco · Accepted solution]

If you have Year as dimension and you want all possible accounts individually for years, set analysis might not work there, because set analysis execution works once on entire chart and not dimension-wise. You can perhaps try 'Show All Values' in dimension tab, if that works.

[inoruoya]

thanks, Michele De Nardi

for your reply, but doesn't work and i can't reduce data for sample so, i will try to reduce or make data for sample.