Expression with a Function not working

[wonkymeister]

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)

[jagan]

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.

[wonkymeister]

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)

[rwunderlich]

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

-Rob

[wonkymeister]

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

[wonkymeister]

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?