topic Re: Two month comparison in QlikView

Two month comparison

Tue, 03 Dec 2013 07:06:52 GMT

Good morning all,

I have a formula, contain a variable (vMaxDate).

In this instance it will show me November 2013 info.

How can I show information for October 2013 next to the column that shows the November 2013 info.

I want to create another variable (vMaxDate2) that will basically show the vMaxDate less one month.

I am not sure about the syntax of the formula.

Please advice the best way I can structure my formula.

Thanks

*R*

Re: Two month comparison

tresB — Tue, 03 Dec 2013 07:18:48 GMT

Is your expression for current month working fine? If so, please post your expression here, it would be easier to modify your expression for second last month. Otherwise, share your sample app.

Re: Two month comparison

Tue, 03 Dec 2013 07:29:30 GMT

Hi there,

Yes my current month is working fine.

This is one of the expression formulas for the current month:

count({<[Created Date] =,Month=,Year=>} if([Created Date] <= vMaxDate,if(Status='L',[Agreement Number])))

Thanks

*R*

Re: Two month comparison

Tue, 03 Dec 2013 07:31:39 GMT

try this set analysis

sum({<DateField={'>=$(Vrolling2)<=$(vMaxDate)'}>}sales)

Vrolling2: =Date(MonthStart(vMaxDate,-1),'DD/MM/YYYY')

vMaxDate: =Date(max(DateField),'DD/MM/YYYY')

Re: Two month comparison

Tue, 03 Dec 2013 07:47:02 GMT

Dear Rentia,

You can using set analysis for this case. Please try this formula:

count({$<Month=,Year=,[Created Date]={">=$(=AddMonths( max([Created Date]),-2 )) <=$(=AddMonths( max([Created Date]),0 ))"}>}if(Status='L',[Agreemenr Number]))

Regards,

Renyta

Re: Two month comparison

tresB — Tue, 03 Dec 2013 07:51:39 GMT

Guessing your vMaxDate is holding the created date one month earlier. If so, you can try:

=count({<[Created Date] =,Month=,Year=, [Created Date]={"<=$(vMaxDate2) >$(vMaxDate)"},Status={'L'}>}[Agreement Number])

Where, vMaxDate2=AddMonths($(vMaxDate), -1)

Re: Two month comparison

CELAMBARASAN — Tue, 03 Dec 2013 08:09:02 GMT

Try this

=Count({<[Created Date] =,Month=,Year=, [Created Date]={"<=$(vMaxDate2)"},Status={'L'}>}[Agreement Number])

Where vMaxDate2 should have =AddMonths(vMaxDate, -1)

Hope it helps

Re: Two month comparison

Tue, 03 Dec 2013 08:22:28 GMT

count({<[Created Date]={'>=$(Vrolling2)<=$(vMaxDate)'},status={'L'}>}[Agreement Number])

Vrolling2: =Date(MonthStart(vMaxDate,-1),'DD/MM/YYYY')

vMaxDate: =Date(max([Created Date]),'DD/MM/YYYY')

Re: Two month comparison

Tue, 03 Dec 2013 09:50:00 GMT

Hi there,

Thank you. This one works for me.

Can you maybe help me with the following formula as well?

sum({<[Created Date]=,Month=,Year=>}if([Created Date] <= vMaxDate2,if(Status='L' and ([Product Type]>'5' or [Product Type]>='A'),NBV)))

Exactly the same scenario…want to show the info for October 2013.

I can just replace the count with sum etc… but I am not quite sure how to type the last bit stating that the product type should be bigger that 5 and bigger that type A.

Thanks

*R*