topic Re: calculated variable in indirect p() statement in QlikView

calculated variable in indirect p() statement

Mon, 05 Nov 2012 15:34:49 GMT

Hi guys,

I very need you help because have a problem which torment me for a long time .

I cannot imaginate how to pass calculated value from variable to the p() statement. I have created a small example, in which I should get the same result in column "second" as it's in "first" column. In "first" column i directly wrote value but in "second" column i have to use variable value.

Thanks for any help.

Re: calculated variable in indirect p() statement

Mon, 05 Nov 2012 15:39:25 GMT

Try:

count({$<CaseId = p({$<StartMonth={'=$(vtestMonth)'}>}CaseId)>}CaseId)

Re: calculated variable in indirect p() statement

swuehl — Mon, 05 Nov 2012 15:45:10 GMT

Change your variable definition to

=makedate(2012,04,01)

(note the equal sign), and your set expression to

count({$<CaseId = p({$<StartMonth={'$(vtestMonth)'}>}CaseId)>}CaseId)

note the single quotes around the dollar sign expansion

Hope this helps,

Stefan

Re: calculated variable in indirect p() statement

Tue, 06 Nov 2012 11:17:08 GMT

Thanks for answers. But I have a question yet. Please see attached file above sample_p()_2.qvw.

P() works only when I've selected filter, but I would like that it works also without selection. My goal is to calculate quantity of that CaseId for each StartMonth where CaseIds are the same as in previous month. Who knows?

Thanks.

Re: calculated variable in indirect p() statement

swuehl — Tue, 06 Nov 2012 11:37:24 GMT

A set expression is evaluated once per chart, not per dimension value, I think this is what you assume here.

So set analysis will not work like expected.

But try maybe something like

=count(aggr(if(CaseId=above(CaseId),CaseId),CaseId,StartMonth))

You just need to assure that the load order of StartMonth is in chronological order (since advanced aggregation will order its dimension values in load order).

Re: calculated variable in indirect p() statement

Tue, 06 Nov 2012 11:57:09 GMT

but if need to calculate unique Caseids for current and previous 2 month (range of 3 months). What the statement should be? Thanks