Re:Calculation of month in Set-Analysis??

[priyav24]

Hello Everybody,

                                   I have an issue regarding the calculation of opening stock,i need to calculate opening stock which is sum of all In's-Sum of all out's where date should be less than current selection of date (ASONDATE).For which i used this formula which is working fine

=num((Sum({$<StockDocDate={"<$(=Date(AsOnDate,'DD/MM/YYYY'))"},IOFlag={'I'}>} StockQty))-(Sum({$<StockDocDate={"<$(=Date(AsOnDate,'DD/MM/YYYY'))"},IOFlag={'O'}>} StockQty)), ' #,##0;(#,##0)')

Now the customer want to calculate for Previous Month based on the  current selection of date (ASONDATE).If user selects the date 06-Jun-2013.Then it should the value for less than' jun-2013' for which i used this formula..

=num((Sum({$<StockDate_MonthYear={"<$(=MonthName((AsOnDate))"},IOFlag={'I'}>} StockQty))-(Sum({$<StockDate_MonthYear={"<$(=MonthName((AsOnDate))"},IOFlag={'O'}>} StockQty)), ' #,##0;(#,##0)')

which is not working...

Field"StockDate_MonthYear" contains values as Jan-2010,Feb-2010.....sep-2013

Thanks in Advance,

Priya

[Gysbert_Wassenaar]

Since you first select a value in AsOnDate you need to undo that in the set analysis expression. So try:

=num((Sum({$<AsOnDate=,StockDate_MonthYear={"<$(=MonthName((AsOnDate))"},IOFlag={'I'}>} StockQty))-(Sum({$<AsOnDate=,StockDate_MonthYear={"<$(=MonthName((AsOnDate))"},IOFlag={'O'}>} StockQty)), ' #,##0;(#,##0)')

But if you're comparing a field with another field in set analysis you may run into another problem. See this document: set_analysis_intra-record.qvw

[priyav24]

Hi gysbert,

                         This is not working,Since AsOnDate is a variable field which is used in calendar object.Herewith i have also attached qvw for your reference...

Thanks in Advance,

Priya

[preminqlik]

hey priya your doc is not opening,

anyway  try this,

table1:

load StockDocDate                                as               IN_OUT_DATE,         

          MonthName(StockDocDate)      as          STOCK_MONTH_YEAR

          IO_FLAG,

          StockQty

resident sourcetable where IO_FLAG='I';

concatenate

load stockDocDate                                 as               IN_OUT_DATE,

          IO_FLAG,

          (StockQty)*(-1)                                as          StockQty

resident sourcetable     where IO_FLAG='O';

drop table sourcetable;

Now in UI open pivot

take your required dimensions and also take IN_OUT_DATE

SUM({$<IN_OUT_DATE= {"<=$(=DATE(([IN_OUT_DATE)))"}>}StockQty)

open list box STOCK_MONTH_YEAR and check

...

-regards

premhas

[somenathroy]

You may use MonthStart func. to get the Start of the month of selected AsOnDate and AddMonths func to get previous month.

e.g

=num((Sum({$<StockDocDate={">=$(=MonthStart(AddMonths(Date(AsOnDate,'DD/MM/YYYY'),-1)))<$(=MonthStart(Date(AsOnDate,'DD/MM/YYYY')))"},IOFlag={'I'}>} StockQty))

-(Sum({$<StockDocDate={">=$(=MonthStart(AddMonths(Date(AsOnDate,'DD/MM/YYYY'),-1)))<$(=MonthStart(Date(AsOnDate,'DD/MM/YYYY')))"},IOFlag={'O'}>} StockQty)), ' #,##0;(#,##0)')

Regards,

som

[priyav24]

PFA...