topic Re: Expression with a Function not working in QlikView

Expression with a Function not working

wonkymeister — Mon, 11 Mar 2013 16:15:14 GMT

newbie and being driven up the wall with set analysis etc

i need to pull through the previous month in my dataset in order to display the previous months headcount of staff. Now this works outside of the expression in a text box, but when i add it as an expression is falls over and i dont know why!!!!!

any help greatly appriciated

Works in textbox:

=Date(addmonths(max(my_standard_date ),-1))

Doesnt work in expression where i need to count last months staff heads:

=sum({<my_standard_date = {'Date(addmonths(max(my_standard_date ),-1))'}>} staff_headcount)

Re: Expression with a Function not working

jagan — Mon, 11 Mar 2013 16:22:09 GMT

Hi,

Try this expression

=sum({<MonthFieldName=, YearFieldName=, QuarterFieldName=, my_standard_date = {'>=$(=Date(MonthStart(max(my_standard_date ),-1)))<=$(=Date(MonthEnd(max(my_standard_date ),-1)))'}>} staff_headcount)

Exclude Month, Year, Quarter fields selection in the expression if you have any in your datamodel.

Hope this helps you.

Regards,

Jagan.

Re: Expression with a Function not working

wonkymeister — Mon, 11 Mar 2013 16:31:39 GMT

thanks jagan - it only pulls back the current month though? it doesnt pull through the prior month.

i tried your script without the month specifics i.e.

=sum({<my_standard_date = {'>=$(=Date(MonthStart(max(my_standard_date ),-1)))<=$(=Date(MonthEnd(max(my_standard_date ),-1)))'}>} staff_headcount)

Re: Expression with a Function not working

rwunderlich — Tue, 12 Mar 2013 05:08:16 GMT

I think you need to add the TOTAL keyword within the sum to have the expression evaluate outside the current dimension.

-Rob

Re: Expression with a Function not working

wonkymeister — Tue, 12 Mar 2013 09:41:09 GMT

qlikview appears to be ignoring whatever is in the set expression

so instead of evaluating what is in the expression to calculate the previous month it is just suming the staff headcount for whatever month the user has selected

Re: Expression with a Function not working

wonkymeister — Tue, 12 Mar 2013 15:27:07 GMT

i'm sure that it has to be a date format issue.

the data i'm pulling through is a datetime field in SQL: DD/MM/YYYY 00:00:00

i wonder if QlikView isnt treating the data as such?