topic Re: Issue with closing month in QlikView

Issue with closing month

ferha_jafri — Thu, 29 Aug 2013 11:32:09 GMT

Hello Everyone,

I have an issue as i have a closing date and closing period fields and my requirement are as when i select the month field the output should be as selected previous month closing date till the selected month closing date eg

when i select the month jan 13 then it should reply the dates from 29-12-2012 to 26-01-2013 them mentioned dates are closing date for the month dec 12 and jan13 .It is very urgent plz reply ..

Thanks in advance

Ferha Jafri

Re: Issue with closing month

vikasmahajan — Thu, 29 Aug 2013 11:44:19 GMT

IF you are looking for like wise comparison between closing months then try following

expression

for CY

SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear))},

[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>} [ YOUR Amount] )

for LY

SUM({$<FinancialYear=,Quarter=,MonthName=,FiscalYear={$(=max(FiscalYear)-1)},

[Posting Date]={">=$(=YearStart(max([Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}>}[ Your Amount])

Hope This Help You

Good Luck

Vikas

Re: Issue with closing month

ferha_jafri — Thu, 29 Aug 2013 11:51:57 GMT

i am not looking for any comparisons i want my month jan to start from last month closing date to selected month closing date.

Re: Issue with closing month

vikasmahajan — Thu, 29 Aug 2013 11:56:35 GMT

Current Month (label :=date(max(MonthYear), 'MMM-YYYY') & chr(10) & '(CM)')

sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))>$(=max({<Year={$(=max(Year))}>} Month)-1)"}>} LineSalesAmount)

Previous Month (Label : =date(addmonths(max(MonthYear), -1), 'MMM-YYYY') & chr(10) & '(PM)' )

sum({$<Year = {$(=max(Year) - $(=if(max(Month) = 1, 1, 0)))}, Month = {$(=month(addmonths(max(MonthYear), -1)))}>} LineSalesAmount)

Re: Issue with closing month

ferha_jafri — Thu, 29 Aug 2013 12:49:20 GMT

I have a closing date for every month ... so when i click on month = jan and Year = 2013 then the data should come from

Closing date of the previous month till the closing date of the current month...........

say 20/12/2012 to 29/1/2013... similarly for every month

Re: Issue with closing month

ferha_jafri — Thu, 29 Aug 2013 12:50:17 GMT

it will be great if some one can attach a sample QVW

Re: Issue with closing month

ferha_jafri — Fri, 30 Aug 2013 05:11:44 GMT

i am attaching the sample qvw file so that my issue became more clear..