topic Re: question on alternate states/addmonths in Year over Year calc in QlikView

question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 02:47:25 GMT

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.

Re: question on alternate states/addmonths in Year over Year calc

Anil_Babu_Samineni — Sat, 20 May 2017 03:17:39 GMT

Why are you looking expression? Instead of that we have always one selected value right. You can go for Alternate state as mention

Re: question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 04:47:48 GMT

Anil:

You are not understanding the problem.

I'm trying to reference a date in default state going back 12 months, in another alternate state.

It's a syntax issue I'm facing.

Gerry.

Re: question on alternate states/addmonths in Year over Year calc

Anil_Babu_Samineni — Sat, 20 May 2017 04:50:29 GMT

Oh, I apologizes for that, Can you share application to look

Re: question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 05:00:02 GMT

Hi anil

see attached.

Re: question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 05:05:50 GMT

change the date to 4/30/16, 4/30/17.

Re: question on alternate states/addmonths in Year over Year calc

avinashelite — Sat, 20 May 2017 05:05:59 GMT

So your object where your referring to the above expression has an alternate state ? if yes then it will be seeking the selection to be in the alternate state mode so this is the syntax

Function({[state_identifier1] <field_name2= [state_identifier2]::field_name3>}field_name)

in your expression I could not see the state 2

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

Re: question on alternate states/addmonths in Year over Year calc

Anil_Babu_Samineni — Sat, 20 May 2017 05:08:30 GMT

Like this?

Re: question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 05:10:06 GMT

HI Anil:

my source state is the default state $.

I'm assigning this in text box which is my "to" state, isn't it implied ?

Re: question on alternate states/addmonths in Year over Year calc

Anil_Babu_Samineni — Sat, 20 May 2017 05:15:46 GMT

Do you follow the same Which Avinash provide the syntax in between the Operator <AlterNateState{

Re: question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 05:19:42 GMT

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

Gerry.

Re: question on alternate states/addmonths in Year over Year calc

Anil_Babu_Samineni — Sat, 20 May 2017 05:21:14 GMT

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

Re: question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 05:26:02 GMT

explicity specifying the function as you have it.

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

does not work.

Re: question on alternate states/addmonths in Year over Year calc

sunny_talwar — Sat, 20 May 2017 11:49:53 GMT

May be try this

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

Re: question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 15:02:05 GMT

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.

Re: question on alternate states/addmonths in Year over Year calc

sunny_talwar — Sat, 20 May 2017 15:24:56 GMT

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)

Re: question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 15:42:49 GMT

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)

Re: question on alternate states/addmonths in Year over Year calc

sunny_talwar — Sat, 20 May 2017 15:46:44 GMT

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?

Re: question on alternate states/addmonths in Year over Year calc

gerrycastellino — Sat, 20 May 2017 16:05:35 GMT

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

Re: question on alternate states/addmonths in Year over Year calc

sunny_talwar — Sat, 20 May 2017 21:48:29 GMT

So all working and good to go?