From_Date and To_Date

[#1]

Hi Guys:

Wish some kind soul we will able to offer some help here.

The user wants to modify the present report with an option of having Date From and Date To.

The original format for Opening Balance figures take account for the beginning of the month. For example: 1/2/2011 Opening balance is 200units and on 4/2/2011 the Opening Balance is 200units. Now the user want to have a range for the Opening Balance for example 1/2/2011 figures is 200units but on 4/2/2011 the Opening Balance is 210 units.

Below is the Expression for the Original report:-

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='20'),InventoryGoodqty))

-

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='34'),InventoryGoodqty))

+

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='40'),InventoryGoodqty))

-

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='42'),InventoryGoodqty))

I am trying the From_date and To-date function but not too sure how it works.

Can some kind soul please help me out of this?

By the way I am not able to use any Set Analysis.

Thanks a lot.

[#2]

Hi Guys:

Some additional information regarding the Expression for the Opening Balance.

The first Expression Opening Balance is the working Expression which take into account for the month start figures.

However, now the needs is for Opening Balance to be any dates that the users select.(From_Date).

Opening Balance

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='20'),InventoryGoodqty))

-

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='34'),InventoryGoodqty))

+

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='40'),InventoryGoodqty))

-

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='42'),InventoryGoodqty))

Change to this

Sum(if(InventoryDate1 <=From_Date  and (invtype='20'),InventoryGoodqty))

-

Sum(if(InventoryDate1 <=From_Date  and (invtype='34'),InventoryGoodqty))

+

Sum(if(InventoryDate1 <=From_Date  and (invtype='40'),InventoryGoodqty))

-

Sum(if(InventoryDate1 <=From_Date  and (invtype='42'),InventoryGoodqty))

The second Expression does not seems to be working.

Could someone please help to look into this.

Thanks alot

[#3]

Hy,

Instead of use "if"  you can use set analysis, it's more performant.

You can find an attached sample who make what you want.

=sum({$<DateCA={">=$(=MonthStart(DateTo))<=$(=Date(DateTo))"}, Product={'Product_4','Product_3','Product_2'}>} CA) 
- sum({$<DateCA={">=$(=MonthStart(DateTo))<=$(=Date(DateTo))"}, Product={'Product_1'}>} CA)

Hope I help you.

Alexandre

[#4]

Hi Alexandre:

Thanks for your reply.

I am not able to use set Analysis as I am using Qlikview 7.5 and it does not has the function for Set Analysis.

Is there any other way to overcome this?

Thank you for your kind help

[qliksus]

Hi ,

if u dont want to use if condition then create a calculated dimension like

=if(date>=fromdate and date<=todate and match(invtype,20,34,42,40) ,date)

and use the expression as sum(InventoryGoodqty)

this will not affect ur performance

[#6]

Hi Qliksus:

Thank you for your reply.

May I know why there is a need to create a calculated dimension?

Can I do everything in the Expression?

Thank you very much once again

[#7]

Hi Guys:

Any helpful suggestion?

Please feel free to comment.

Thanks alot guys

[#8]

just try this if it suites your  requirement..

a bit of clumsy way tosolve this problem.. but works wonder

..

1. create a calender.. using this code for FROMDATE and also create TODATE similarly having two diff tbl names

///////////************************////////////////

FOR i = 2007 TO 2012

FOR J = 1 TO 12

auto_date:

load

$(i) as year,

$(J) AS Month,

recno() as day,

DATE(makedate($(i),$(J),recno()),'DD-MM-YYYY') as FROM_DATE,

autogenerate (31);

    NEXT J;

NEXT i;

//*****************/////

and then pass this thru your expression and compare with expression_date

[#9]

Hi Sangyt:

Thanks for your reply.

After keying in the Expression below I am having an Error message:-

Allocated memory exceeded.

Sum(if(InventoryDate1 <=From_Date  and (invtype='20'),InventoryGoodqty))

After reading the Forum it does not seems to be able to have an answer for this Allocated Memory Exceeded Error.

Can any one here able to help to explain why so?

Thanks a lot guys

[#10]

Hi Qliksus:

Thank you for your reply.

May I know why there is a need to create a calculated dimension?

Can I do everything in the Expression?

Thank you very much once again