topic Re: If statement for Month selection in QlikView

If statement for Month selection

Karim_Khan — Tue, 18 Aug 2015 10:57:56 GMT

Hi Team,

I wanted to use If statement for Month selection in expression.

Currently My calculation is working as below

=sum({$<FiscalMonth=>}[Net Bookings])

and expression

=AGGR(IF(Rank(sum([Net Bookings]), 4)<=5, [End Customer Global Ultimate Name]),[End Customer Global Ultimate Name])

When I click on Month then values gets associated from Fiscal Month

Month,Fiscal Month

Oct,3

Nov,4

Feb,7

Mar,8

But I want to Apply if condition so that If I will select the Month May then it should pick up the 3

May,3

Jun,4 and so on.

I want this dynamically.

Pls help me out

Regards,

Re: If statement for Month selection

gautik92 — Tue, 18 Aug 2015 11:01:15 GMT

try loading inline table

Re: If statement for Month selection

Anonymous — Tue, 18 Aug 2015 11:14:04 GMT

use mapping inline table together with applymap

TEST:

mapping load * Inline [

Month, Seq

May, 3

June, 4

...

];

in table

load

abc,

applymap('TEST',yourmonth) as SeqNr

def,

resident ...

Re: If statement for Month selection

sasiparupudi1 — Tue, 18 Aug 2015 11:30:16 GMT

use the expression

FiscalMonth={"$(=date(addmonths(date#(Month,'MMM'),-2),'MMM'))"}

ex:date(addmonths(date#('May','MMM'),-2),'MMM')

Re: If statement for Month selection

qlikviewwizard — Tue, 18 Aug 2015 11:49:39 GMT

Can you please attach the sample file. It would be easier to provide the solution. Thank you.

Re: If statement for Month selection

Karim_Khan — Tue, 18 Aug 2015 11:57:06 GMT

But Sir will it work for dynamic month

Re: If statement for Month selection

sasiparupudi1 — Tue, 18 Aug 2015 12:02:02 GMT

yes it should work if your fiscal month is always 2 months behind the actual month value

Re: If statement for Month selection

Karim_Khan — Tue, 18 Aug 2015 12:11:48 GMT

it is working.But If I wanted to apply this login in Fiscal Month then.

It has the Numeric Value

1,2,3,4,....12.

Its code is as follows

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1

I wanted to apply the above logic to this

Re: If statement for Month selection

Anonymous — Tue, 18 Aug 2015 12:20:56 GMT

Hi Karim,

Try :

Pick(Match(MONTH,'May','Jun','Jul',........), 3,4,5,........)

Regards

Neetha

Re: If statement for Month selection

sasiparupudi1 — Tue, 18 Aug 2015 12:28:48 GMT

what are the values in $(vFiscalYearStartMonth) ?

Re: If statement for Month selection

Karim_Khan — Tue, 18 Aug 2015 12:30:05 GMT

Re: If statement for Month selection

sasiparupudi1 — Tue, 18 Aug 2015 12:38:02 GMT

=num(month(date(addmonths(num(Month(Date)) ,- 2),'MMM')))

Re: If statement for Month selection

Karim_Khan — Tue, 18 Aug 2015 12:44:41 GMT

Sir value is not getting changed while selecting the month.

Its got fixed to one specific number.

like 11

Re: If statement for Month selection

sasiparupudi1 — Tue, 18 Aug 2015 12:51:02 GMT

please try

num(month(date(addmonths(date#(Date,'MMM'),-2),'MMM')))

Re: If statement for Month selection

Karim_Khan — Tue, 18 Aug 2015 12:59:04 GMT

I am trying to put the Fiscal Month but it is not getting calculated

=num(month(date(addmonths(date#(FiscalMonth,'MMM'),-2),'MMM')))

Re: If statement for Month selection

sasiparupudi1 — Tue, 18 Aug 2015 13:05:37 GMT

sorry I am not following what you want to try here.

the expression num(month(date(addmonths(date#(Date,'MMM'),-2),'MMM')))

should give you fiscalmonth for a selected date in the number format 1,2,3, 4 etc..

if you want to subtract 2 month from the fiscal month then the following should work

=num(month(date(addmonths(date#(FiscalMonth,'MM'),-2),'MMM')))

hth

Sasi