topic Re: Simplifying a nested if expression in QlikView

Simplifying a nested if expression

Mon, 17 Mar 2014 19:47:09 GMT

Hello

This expression of mine is trying to calculate 12 months rolling but is not coming out correctly.

If anyone can change this expression it would be helpful

If([Currency]='Transactional',

Sum ($(vfsa-Deferred Revenue)If([GL Date] >= date("$(=[End Period])"+12) and [Initial Entry Flag]='N'and [Schedule Created End Date] < date("$(=[End Period])"+12) and [Schedule On Hold]='N' ,[Net Amount],0)),

If([Currency]='Functional',

Sum ($(vfsa-Deferred Revenue)If([GL Date]>= date("$(=[End Period])"+12) and [Initial Entry Flag]='N'and [Schedule Created End Date] < date("$(=[End Period])"+12) and [Schedule On Hold]='N',[Net Amount Operating Currency],0)),

If([Currency]='Reporting',

Sum ($(vfsa-Deferred Revenue)If( [GL Date] >= date("$(=[End Period])"+12) and [Initial Entry Flag]='N'and [Schedule Created End Date] < date("$(=[End Period])"+12) and [Schedule On Hold]='N',[Net Amount Reporting Currency],0)),0

// Sum ($(vfsa-Revenue)If([GL Date]>= $(=[StartPeriod]) AND [GL Date]<= ($(=[EndPeriod])+12),[Net Amount],0))

)

Thanks

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 20:07:47 GMT

Hey Rizwan,

A few suggestions I can think of.

You can create a flag on the back-end to determine the rolling 12 months of data so in the expression you can just use DateFlag=1
You can create a variable like vCurrency = if(Currecny=Transactional,'[Net Amount]',if(Currency=Functional,'[Net Amount Operating Currency]','[Net Amount Reporting Currency]') )
I am assuming you have a filter to choose currency type so based on what type is selected the relevant net amount field would be selected
The expression would be

Sum ($(vfsa-Deferred Revenue)If( [Initial Entry Flag]='N'and DateFlag =1 and [Schedule On Hold]='N' ,$(vCurrency))

Note: This just gives you an idea of how to go about doing this. You might have to tweak it as per your needs if you run into any syntactical errors. Not sure what $(vfsa-Deferred Revenue) is and why its used there.

That seems incorrect.

Thanks

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 20:10:25 GMT

Hello

Can you please reply how do I create a DateFlag in this case in the backend.

Thanks

On Mon, Mar 17, 2014 at 1:08 PM, Ajay Prabhakaran

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 20:31:10 GMT

I dont know what GL Date and Schedule created End date means but here's a general idea. If you have a date field called Created Date then,

Table:

Load *, if(Created Date>= Date(addmonths(today(),-12) ),1) as DateFlag

From Table;

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 20:41:37 GMT

Hello Ajay

So in my current expression I have two essential condition

>= date("$(=[End Period])"+12)

*and *

< date("$(=[End Period])"+12)

*Here *"$(=[End Period])" is nothing but a field END_DATE

*So now please guide me how would i replicate this as per the DateFlag

condition you sent me.*

Thanks

On Mon, Mar 17, 2014 at 1:31 PM, Ajay Prabhakaran

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 21:04:42 GMT

I see what you are doing. You want to calculate the rolling 12 months based on user selection. It doesn't always have to be 12 months back from today.

In that you have to use those expressions. Maybe you can just remove the redundant if() conditions as mentioned above.

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 21:09:14 GMT

Yes thats correct

My point is

in your expression

Load *, if(Created Date>= Date(addmonths(today(),-12) ),1) as DateFlag

How do I incorporate my conditions instead of Created Date and today. The

conditions are

>= date("$(=[End Period])"+12)

*and *

< date("$(=[End Period])"+12)

*Here *"$(=[End Period])" is nothing but a field END_DATE

*if(.................................) as dateflag for my conditions would

be how?*

Thanks

On Mon, Mar 17, 2014 at 2:05 PM, Ajay Prabhakaran

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 21:15:21 GMT

The DateFlag can't be used for your situation as the rolling 12 months can change based on user selection. So your expression would be something like:

Sum ($(vfsa-Deferred Revenue)If( [Initial Entry Flag]='N'and [GL Date] >= date("$(=[End Period])"+12) and [Schedule Created End Date] < date("$(=[End Period])"+12) and [Schedule On Hold]='N' ,$(vCurrency))

Thanks

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 21:23:02 GMT

Hello Ajay

Maybe I am not able to make myself clear

See let me explain again

*date("$(=[End Period])"+12) is not working the way I want it to work. It

is not doing a rolling 12 months.*

*So you told me to create a field called DateFlag in the backend and use it

in my expression.*

I can do that.

I just want to know in your expression for date flag how do i

incorporate my conditions*

>= date("$(=[End Period])"+12) and

< date("$(=[End Period])"+12)*

So basically replace

Load *, if(Created Date>= Date(addmonths(today(),-12) ),1) as DateFlag

*with *

*Load ,if("My Conditions") as DateFlag

I hope I am clear

Thanks

On Mon, Mar 17, 2014 at 2:15 PM, Ajay Prabhakaran

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 21:33:49 GMT

Yes Rizwan,

Initially I suggested a back-end calculation thinking the data was gonna be static(12 months from today). But it seems like thats not the case. So you would have to do it on the front end(because when user selects End Period as say March 2012 then data needs to be from March 2011 - March 2012, similarly if user selects May2013, then data from May2012 - May 2013). It needs to be used in the expression. But if its not giving any results then maybe can you

attach your sample qvw.

Also whats the purpose of $(vfsa-Deferred Revenue) variable?

Thanks

Re: Simplifying a nested if expression

Mon, 17 Mar 2014 21:38:38 GMT

Vfsa-Deferred Revenue is nothing but a variable that has a condition stored

behind it

(='DEF or ='EITF DEF')

Thx

On Mon, Mar 17, 2014 at 2:34 PM, Ajay Prabhakaran