topic Re: Having trouble with text box expression in QlikView

Having trouble with text box expression

Tue, 17 Nov 2015 17:37:29 GMT

Hello all,

First off, thank you to those that have already provided excellent help for my previous issues.

I currently have a text box that I use to calculate the percentage of "Satisfied" answers on a given survey. The data that I'm pulling from has responses from several different timestamps (ReportPeriod) and I had them hard-coded into my expression like so:

=Num(count({<Answer = {'Satisfied','More Productive'}, ReportPeriod = {'2014-10-01'}>}Question) / count({<ReportPeriod = {'2014-10-01'}>}Question), '##.##%')

This works wonderfully, but I don't want to have to go back and re-input the dates every time there is a new survey -- so I now have a field called CurrentPeriod, which pulls the most recent date. However, I can't figure out how to get that field into my expression above to make it work as it's currently working.

Any help would be greatly appreciated!

Thanks,

Tim

Re: Having trouble with text box expression

swuehl — Tue, 17 Nov 2015 18:01:56 GMT

Try

=Num(count({<Answer = {'Satisfied','More Productive'}, ReportPeriod = p(CurrentPeriod)>}Question) / count({<ReportPeriod = p(CurrentPeriod) >}Question), '##.##%')

assuming that CurrentPeriod field has only one value, the current period, in same format as ReportPeriod.

Re: Having trouble with text box expression

sunny_talwar — Tue, 17 Nov 2015 18:04:54 GMT

May be this:

=Num(count({<Answer = {'Satisfied','More Productive'}, ReportPeriod = {"$(=Date(CurrentPeriod, 'YYYY-MM-DD'))"}>}Question) / count({<ReportPeriod = {"$(=Date(CurrentPeriod, 'YYYY-MM-DD'))"}>}Question), '##.##%')

Re: Having trouble with text box expression

Tue, 17 Nov 2015 18:10:30 GMT

Yep, that did it! Any chance you can explain what the 'p' does before the field entry, and why it's needed? I just want to make sure I'm learning as I go, and not just relying on the community for help

Re: Having trouble with text box expression

sunny_talwar — Tue, 17 Nov 2015 18:46:48 GMT

p(CurrentPeriod) is used for all possible values within CurrentPeriod should be used to filter ReportPeriod. e(CurrentPeriod) is to include all excluded values from CurrentPeriod should be used to filter ReportPeriod.

Look at the attached for more detailed explanation