calculated variable in aggr

[curiousfellow]

I have this formula :

=count(distinct {<sales={'>0'}>} name)/aggr(nodistinct count({<sales={'>0'}>} distinct name),$(groupmonth))

It doesn't work, when i use group and month  seperate

=count(distinct {<sales={'>0'}>} name)/aggr(nodistinct count({<sales={'>0'}>} distinct name),group,month)) it works

Probably this not a very good example, but all I want to know if it is possible to use a calculated variable this way.

[sunny_talwar]

How do you define $(groupmonth)? May be you need an equal sign within the dollar sign expansion

=Count(DISTINCT {<sales={'>0'}>} name)/Aggr(NODISTINCT Count({<sales={'>0'}>} DISTINCT name),$(=groupmonth))

[curiousfellow]

It is just a simple expression group&month

I group and month as a dimension in the same pivot table in which the expression is used

It is from this dataset :

load * Inline

[

month ,group,name,sales

jan,a,john,3000

jan,a,john,300

jan,a,carl,0

jan,a,dave,10

jan,a,mary,50

jan,b,justin,200

jan,b,justin,300

jan,a,boy,50

feb,b,pete,10

feb,b,pete,50

feb,a,john,50

feb,a,john,20

feb,a,carl,8

feb,a,dave,80

feb,a,mary,0

feb,b,justin,10

feb,b,justin,15

feb,a,boy,80

feb,b,pete,150

feb,b,pete,60

feb,b,paul,100

];

[sunny_talwar]

Why would you combine them? Why not use group, month? Would you be able to share you logic behind trying to combine them group&month?

[curiousfellow]

This exmple is just for testing purposes. In the app i am developing, the "groupmonth" is more complicated

Something like :

=(num((((left(MasterCalander.yearstring,4)))-(num(year_group)-1)),'00'))&right(MasterCalander.PeriodName,2)

So I hope you will understand I rather have a variable for this (although this expression doesn't work either in the aggr-expression

[sunny_talwar]

That probably won't work because aggr function expects a field which is created in the script. It doesn't allow to use a calculated dimension.

[curiousfellow]

Well, that answers my question . I am going to struggle on (isn't it the fun of this all )

Thank you for your answer

[sunny_talwar]

No problem at all and sorry I didn't bring you the good news

Best,

Sunny