topic Re: Can you use Applymap in application syntax? Help with input values. in QlikView

Can you use Applymap in application syntax? Help with input values.

Fri, 04 Jan 2013 17:24:26 GMT

Hi all

Hoping for some help on an issue I am having...

Users are asked to input a start and end date in the format of MMYY into an input box (with predefined values). These input values are then used in a range to sum up values. The problem I face is that the predefined values do not sort correctly and so, for example, if the user inputs a start date of '0312' (to represent March 2012) and an end-date of 0412 (April 2012) the months of 0410 and 0411 are also includes in the sum function.

Is there a way to apply a map to return a corresponding (sequential) number already in my model that represents the Month¦Year inputted fields for the sum expression?

The expression I am using is below and the image shows the issue in tabular detail

I only want the values associated to 0312 and 0412 to be shown in the 'Billing ref' object - and not the additional two lines of 0410 and 0411 - which are incorrectly in the range that the user has entered. By replacing variable with the corresponding '_sequence' field I would resolve my issue. Would apply map work here?

Any help is appreciated.

Thanks

Scott

sum({$< _calendarperiod = {">=$(=_vFromPeriod3)<=$(_vToPeriod3)"}>} Billed_Hours  )

Re: Can you use Applymap in application syntax? Help with input values.

Gysbert_Wassenaar — Fri, 04 Jan 2013 17:31:02 GMT

Applymap works only in the script. If you want only two records shown for only the from period and to period try

sum({$< _calendarperiod = {$(_vFromPeriod3),$(_vToPeriod3)}>} Billed_Hours )

If you want the correct range between them you'll have to create a different _calendarperiod field that does sort correctly, i.e. YYMM or YYYYMM and wrangle your variables into that format. Even easier would be to ask your users to enter their selection in a YYMM format or use year and month listboxes instead of your input boxes.

Re: Can you use Applymap in application syntax? Help with input values.

Fri, 04 Jan 2013 17:36:22 GMT

Thanks Gysbert - but I would need a range and not only the two values in the input fields. So for example if From date is 0112 and end date is 0412 I would want 0112, 0212, 0312 and 0412 to be returned.