topic Re:Calculation of month in Set-Analysis?? in QlikView

Re:Calculation of month in Set-Analysis??

priyav24 — Mon, 23 Sep 2013 10:04:06 GMT

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

Re:Calculation of month in Set-Analysis??

Gysbert_Wassenaar — Mon, 23 Sep 2013 18:50:27 GMT

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

Re:Calculation of month in Set-Analysis??

priyav24 — Tue, 24 Sep 2013 05:59:58 GMT

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

Re:Calculation of month in Set-Analysis??

preminqlik — Tue, 24 Sep 2013 09:40:40 GMT

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

Re:Calculation of month in Set-Analysis??

somenathroy — Tue, 24 Sep 2013 12:31:13 GMT

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

Re: Re:Calculation of month in Set-Analysis??

priyav24 — Tue, 24 Sep 2013 12:55:25 GMT

PFA...