topic Re: calculated variable in aggr in QlikView

calculated variable in aggr

curiousfellow — Fri, 07 Oct 2016 13:44:35 GMT

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.

Re: calculated variable in aggr

sunny_talwar — Fri, 07 Oct 2016 13:48:29 GMT

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))

Re: calculated variable in aggr

curiousfellow — Fri, 07 Oct 2016 17:29:08 GMT

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

];

Re: calculated variable in aggr

sunny_talwar — Fri, 07 Oct 2016 17:30:49 GMT

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?

Re: calculated variable in aggr

curiousfellow — Fri, 07 Oct 2016 17:41:10 GMT

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

Re: calculated variable in aggr

sunny_talwar — Fri, 07 Oct 2016 17:42:50 GMT

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.

Re: calculated variable in aggr

curiousfellow — Fri, 07 Oct 2016 17:49:06 GMT

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

Thank you for your answer

Re: calculated variable in aggr

sunny_talwar — Fri, 07 Oct 2016 17:55:30 GMT

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

Best,

Sunny