Hi All,
I have one requirement I have to find the Value of last month based on the Inventory Closed. The Scenario is as follows
I have two tables in first table Transtype field which indicates the Inventory Closed status where Transtype=7 which is not present in second table and now i have to find the value of Measure B which is present in the second Table B. I have to caluclate the value of Measure B based on the Inventory Closed means last month Value of Mesure B becuase Current Month is always not Closed so we have to refer the value of Measure B of Previous Month where Inventory Status is Closed . If any Case the Previous Month is also not Closed than i have to refer previous to previous Month.
I have giving the table description below and I have tried i had used one variable tmp in the my test Qvw in which i used set analysis
variable defination(max({<TRANSTYPE={'7'}>}Month)) which indicates the Month Closing . In my Case when I select July it shows null value and not showing the Value of July i.e. Value of June in that Case In June Inventory is Closed. So,please help me to resolve the Issue.
So, Please help me how we achieve that using set analyis.
Any Help would be appreciated.
Temp:
LOAD ITEMID,
DATEPHYSICAL,
Date(Floor(DATEPHYSICAL)) as [Trans Date],
year(DATEPHYSICAL) as Year,
num(Month(Floor(DATEPHYSICAL))) as Month,
Month(Floor(DATEPHYSICAL)) as MonthName,
QTY,
TRANSTYPE, //indicates the Inventory Closed Status where Transtype=7
TRANSREFID,
INVOICEID,
VOUCHER
FROM
(qvd)Where DATAAREAID=100 AND Year(DATEPHYSICAL)<>1900;
Concatenate
Concatenate
LOAD
FROMADDRESSSTATENAME as BName,
FROMTALUKANAME as BFROMTALUKANAME,
TOTALUKANAME as BTOTALUKANAME,
Date(Floor(EFFECTIVEDATETIME)) as [Trans Date],
Month(Floor(EFFECTIVEDATETIME)) as MonthName,
num(Month(Floor(EFFECTIVEDATETIME))) as Month,
year(EFFECTIVEDATETIME) as Year,
DISTANCE as [Measure B]
FROM
(qvd)Where DATAAREAID=100;
Concatenate
LOAD
FROMADDRESSSTATENAME as BName,
FROMTALUKANAME as BFROMTALUKANAME,
TOTALUKANAME as BTOTALUKANAME,
Date(Floor(EFFECTIVEDATETIME)) as [Trans Date],
Month(Floor(EFFECTIVEDATETIME)) as MonthName,
num(Month(Floor(EFFECTIVEDATETIME))) as Month,
year(EFFECTIVEDATETIME) as Year,
DISTANCE as [Measure B]
FROM
(qvd)Where DATAAREAID=100;
Regards
Deepak
