Logic

[panipat1990]

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

[vinieme12]

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

(ooxml, embedded labels, table is Sheet1);

Dimension

Product

Expression

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

,CurrFiscalFlag = {1}>} Net_Value)

[panipat1990]

Dear sir,

where will  come  from   Currfiscalflag .kindly  send  me  snap  shot 

[vinieme12]

See attached App

also read

Fiscal Year

[panipat1990]

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 . ....

[vinieme12]

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)

[panipat1990]

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

[vinieme12]

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

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

  >}Net_Value)

[panipat1990]

Sir,This expression not working.

[vinieme12]

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)