13 Replies Latest reply: Nov 26, 2016 2:49 PM by Vineeth Pujari

# Logic

Dear Team,

I stuck a issue. My Requirement is

Suppose we have a Sale Data from Apr to Jul

And I want to Result

When a person select May's any date then Sale would be Sum of From (01/04/2016 to 30/04/2016) Sale Showed in Report.

But When i select Apr's Month Any Date then it Showing All Sale.

But it should be sum of (01/04/2016 to 31/03/2016)   Sale Zero.

but it is showing All Sale Value. Kindly Help me only for Solevingthis Requirement.

For this i have make two Variable

1. V_End_Month= Date(MonthStart(RangeMin(Date(Floor(Max(Link_Date)),'DD/MM/YYYY'),Date(Today()-1,'DD/MM/YYYY')),0)-1,'DD/MM/YYYY')

2.v_YearStart= DATE(MonthStart(YEARSTART(vCurrentDate,0,4)),'DD/MM/YYYY')

For Making vCurrent Date I use this Formula

vCurrentDate=DATE(RANGEMIN(DATE(FLOOR(MAX(Link_Date)),'DD/MM/YYYY'),DATE(TODAY()-1,'DD/MM/YYYY')),'DD/MM/YYYY')

And I am using Expression in frontend

=Sum({\$<Link_Date={"<=\$(V_End_Month)>=\$(vYearStart)"},[Fiscal Year],[FY Year Name],[Fiscal Quarter],[Fiscal Month]>} Net_Value)

For this i am sharing excel.And Qvw Maker

• ###### Re: Logic

In Addition to below Add a master calendar for flagging Current Fiscal Year and add it your Set Expression

Script

LOAD Link_Date,

Net_Value,

Product,

NUM(Month(Link_Date),'##') as MonthIS,

NUM#(Year(Link_Date),'####') as YearIS

FROM

[F:\CommunityHelp\241107.xlsx]

(ooxml, embedded labels, table is Sheet1);

Dimension

Product

Expression

sum({<Link_Date=,MonthIS = {'\$(=month(Max(Link_Date))-1)'}

,CurrFiscalFlag = {1}>} Net_Value)

• ###### Re: Logic

Dear sir,

where will  come  from   Currfiscalflag .kindly  send  me  snap  shot

• ###### Re: Logic

See attached App

also read

Fiscal Year

• ###### Re: Logic

But  it  show  separate  month  sale . .but  I  want sale sum from   year  start......

like  if  I  select  5May  then  sale  sum  of  from  01/04/2016  to  3 Apr . .

If  I  select  5Jun  then  sale  sum  of  from 01/04/2016  to  30may....

problem  is  coming  if  I  select  any  date  of  April  then  it  show  current  year  sale . ....

• ###### Re: Logic

complete expression

sum({<Link_Date=,MonthIS = {'<\$(=month(Max(Link_Date)))'}

,fYear = {'\$(=if((month(Max(Link_Date))-1)<4,Year(Link_Date),Year(Link_Date)+1))'}

>}Net_Value)

• ###### Re: Logic

If  I  add  date  07-01-2017  then  it  showed  0....

• ###### Re: Logic

sum({<Link_Date=,fMonth = {"<\$(fMonth)"}

,fYear = {'\$(=if((month(Max(Link_Date))-1)<4,Year(Link_Date),Year(Link_Date)+1))'}

>}Net_Value)

• ###### Re: Logic

Sir,This expression not working.

• ###### Re: Logic

sum({<Link_Date=,fMonth = {"<\$(=if(fMonth=1,13,fMonth))"}

,fYear = {'\$(=if((month(Max(Link_Date))-1)<4,Year(Link_Date),Year(Link_Date)+1))'}

>}Net_Value)

• ###### Re: Logic

See attached updated APP

• ###### Re: Logic

Not Working this expression

• ###### Re: Logic

can you share your sample app! it's working for me, have you seen the attachment?