Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hey Rizwan,
A few suggestions I can think of.
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
AJ
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
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;
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
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.
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
Thanks
On Mon, Mar 17, 2014 at 2:05 PM, Ajay Prabhakaran
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
AJ
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
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