question on alternate states/addmonths in Year over Year calc

[gerrycastellino]

having a problem with my syntax below.

My default state is where I have the most recent month selected - see image.

I'm trying to go back 12 months to  grab a value from a year ago from my default state.

I have the below expr linked to alternate state which is not working.

=sum({< %Snapshot_Date = {"$(=addmonths($::%Snapshot_Date,-12,1))"} >} Headcount) 

What am I missing ?

Thankx !

Gerry.

[gerrycastellino]

I follow it, but that's what I need help in, which is my syntax, when I use addmonths, something is missing.

Gerry.

[Anil_Babu_Samineni]

I am not sure what does the use of 1 with in between the AddMonths Parenthesis??

[gerrycastellino]

explicity specifying the function as you have it.

=sum({[State1]<date1 = {"$(=addmonths([state2]::Date1,-12,1))"} >}sales) 

does not work.

[sunny_talwar]

May be try this

=Sum({<date1 = {"$(=Date(AddMonths(Only({$} date1), -12), 'MM/DD/YYYY'))"}>}sales)

[gerrycastellino]

Sunny:

this has worked beautifully !

Thank you.

A follow up I have is, how do I override the default set on a dimension, like this:

=Sum({<emptype={'PT'}, Date1 = {"$(=Date(AddMonths(Only({$} Date1), -12), 'MM/DD/YYYY'))"}>}sales) 

where I'm overriding the 'FT'  to 'PT'.

Gerry.

[sunny_talwar · Accepted solution]

QlikView is case sensitive and you date field is called date1 and not Date1 (see the upper case D vs lower case d)..

This worked

=Sum({<emptype={'PT'}, date1 = {"$(=Date(AddMonths(Only({$} date1), -12), 'MM/DD/YYYY'))"}>}sales)

whereas this didn't

=Sum({<emptype={'PT'}, Date1 = {"$(=Date(AddMonths(Only({$} Date1), -12), 'MM/DD/YYYY'))"}>}sales)

[gerrycastellino]

ok, that worked well !

another follow up.

I'm doing something very similar in my real data set with something like this:

I'm reading the defaults from the current state, and overriding workertype ='Contingent worker'

- it doesn't like the worker_type construct.

=Sum({<
Worker_Type={'Contingent Worker'},
Location=$::Location,
Region=$::Region,
JobProfile=$::JobProfile,
JobLevel=$::JobLevel,
JobFamilyGroup=$::JobFamilyGroup,
Employee_Type=$::Employee_Type,
%Snapshot_Date = {"$(=Date(AddMonths(Only({$} %Snapshot_Date),-12), 'MM/YYYY'))"}>}Ending_Headcount) 

[sunny_talwar]

Don't see anything wrong here, are you sure it is Worker_Type and not worker_type or something else? Also, why do you think it doesn't like Worker_Type? Do you see any red wiggly line or something similar?

[gerrycastellino]

was my syntax, I reduced it down to :

=Sum({<

//Location=$::Location,
//Region=$::Region,
//JobProfile=$::JobProfile,
//JobLevel=$::JobLevel,
//JobFamilyGroup=$::JobFamilyGroup,
//Employee_Type=,
Worker_Type={'Contingent Worker'},
%Snapshot_Date = {"$(=Date(AddMonths(Only({$} %Snapshot_Date),-12), 'MM/YYYY'))"}>}

Ending_Headcount) 

and it seemed to work

[sunny_talwar]

So all working and good to go?