Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

panipat1990
Not applicable

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

13 Replies
vinieme12
Not applicable

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

(ooxml, embedded labels, table is Sheet1);

Dimension

Product

Expression

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

,CurrFiscalFlag = {1}>} Net_Value)

panipat1990
Not applicable

Re: Logic

Dear sir,

where will  come  from   Currfiscalflag .kindly  send  me  snap  shot 

vinieme12
Not applicable

Re: Logic

See attached App

also read

Fiscal Year

panipat1990
Not applicable

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

vinieme12
Not applicable

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)

panipat1990
Not applicable

Re: Logic

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

vinieme12
Not applicable

Re: Logic

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

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

  >}Net_Value)

panipat1990
Not applicable

Re: Logic

Sir,This expression not working.

vinieme12
Not applicable

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)