topic Re: Current and previous month issue in QlikView

Current and previous month issue

Mon, 27 Jan 2014 17:16:30 GMT

Hi All,

How we can get the total count and total value of the current month and previous month.Let's say ex.

I have the list boxes like;

Month,Period,Account open date,balance,key

I want to calculate the above mentioned calculations.

If i select the month it has to to show 1) the selected month count of total AOD's and total value in that month and the previous month as well.

i have written the following script in pivot table.

present MTD(Count) : Count({< Account open date ={"<=$(=max(period)>=monthstart(max(period))"} distinct key (key is not creating any issue)

present MTD(Value) : sum({< Account open date ={"<=$(=max(period)>=monthstart(max(period))"} if(balance<0,0,balance)/1000

previousMTD(Count) : Count({< Account open date ={"<=$(=monthend(addmonths(max(period),-1)))>=monthstart(addmonths(max(period),-1)))"} distinct key

previous MTD(value) :Count({< Account open date ={"$(=(monthend(addmonths(max(period),-1)))>=monthend(monthstart(max(period),-1)))"} if(balance<0,0,balance)/1000

Note: The problem am getting at previous month value is showing wrong.The present month count, value and previous month count is ok.

Say for ex if am selecting DEC;

previous month value is showing the total value i.e. DEC as 900 and NOV as 7000

But if am selecting the NOv its showing exact vale NOV as 3000 and oct as 900

same op/p for oct as well.

What might be the possble chances for this case or any thing wrong in my expressions.

Sorry for missing the attachment am unable to attach the attachment..

Thanks in adv,

Ravi

Re: Current and previous month issue

jerem1234 — Mon, 27 Jan 2014 17:28:26 GMT

I think your < and > are a little off in the set analysis.

Should be like this:

sum({< Account open date ={"<=$(=max(period))>=$(=monthstart(max(period)))"} if(balance<0,0,balance)/1000

Try that for each one of your expressions and let me know.

Hope this helps!

Re: Current and previous month issue

Mon, 27 Jan 2014 17:38:51 GMT

Thanks Jerem.....your right...i have forget to mentioned here.

But the problem am getting at previous month value level.

previous MTD(value) :Count({< Account open date ={"<=$(=(monthend(addmonths(max(period),-1)))>=(monthstart(addmonths(max(period),-1))))"} if(balance<0,0,balance)/1000

It gives different result.Is there any thing wrong in that to get the prev month count and value.

previous month value is showing the total value i.e. DEC as 900 and NOV as 7000

But if am selecting the NOv its showing exact vale NOV as 3000 and oct as 900

Thanks

Ravi

Re: Current and previous month issue

jerem1234 — Mon, 27 Jan 2014 17:55:37 GMT

Did you correct the errors I mentioned above? It may cause it to show wrong numbers:

Count({< [Account open date] ={"<=$(=monthend(addmonths(max(period),-1)))>=$(=monthstart(addmonths(max(period),-1)))"}if(balance<0,0,balance))/1000

For value:

Count({< [Account open date] ={">=$(=monthend(addmonths(max(period),-1)))>=$(=monthend(monthstart(max(period),-1)))"} if(balance<0,0,balance))/1000

Let me know if that fixes it. Otherwise I cant be sure, seems like the above formula should work. Are your dates in date format?