# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for
Did you mean:
Creator II

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

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

Priya

5 Replies

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

talk is cheap, supply exceeds demand
Creator II
Author

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

Priya

Specialist II

hey priya your doc is not opening,

anyway  try this,

table1:

MonthName(StockDocDate)      as          STOCK_MONTH_YEAR

IO_FLAG,

StockQty

resident sourcetable where IO_FLAG='I';

concatenate

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

Creator III

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

e.g