topic Re: How to write the expression ? in QlikView

How to write the expression ?

suvechha_b — Tue, 25 Mar 2014 09:31:44 GMT

I have declare the following :

MonthPeriod:

Load * inline [

Fiscal Month,Period

Jul,1

Aug,2

Sep,3

Oct,4

Nov,5

Dec,6

Jan,7

Feb,8

Mar,9

Apr,10

May,11

Jun,12

];

one variable : vPrevious :Month(AddMonths(Date(Today()),-1)) which gets the value " feb "

I want to use the value 8 from the inline function MonthPeriod for the below expression :

sum({<[fis_period] = {8} >}[Stag Forecast Units])

as fis_period use 8 instead of feb .

But the above query is incorrect . Please help me to write the expression.

Re: How to write the expression ?

MK_QSL — Tue, 25 Mar 2014 09:35:01 GMT

SUM({<[fis_period] = {'$(=NUM(Month(AddMonths(Today(),-1))))'}>}[Stag Forecast Units])

Re: How to write the expression ?

michael_maeuser — Tue, 25 Mar 2014 09:37:06 GMT

you need to add a group by --> all fields that are not part of an aggregation in this table, e.g.

load

DimA,

DimB

sum({<[fis_period] = {8} >}[Stag Forecast Units]) as fis_period use 8 instead of feb

group by DimA, DimB

sql selectz...;

Re: How to write the expression ?

sushil353 — Tue, 25 Mar 2014 09:37:40 GMT

are you getting any error.

Expression above seems correct.. please explain what issue you are getting.

Re: How to write the expression ?

suvechha_b — Tue, 25 Mar 2014 09:38:14 GMT

But this expression is giving value 2 . I dont want that ....

I want Feb,8 thats why I wrote the Inline function.

How to get feb = 8 in the above expression.

Re: How to write the expression ?

jsingh71 — Tue, 25 Mar 2014 09:43:35 GMT

Find Attachment.

In expression change your dimension name.

Instead of fis_period use Period.

--Jai

Re: How to write the expression ?

PrashantSangle — Tue, 25 Mar 2014 09:44:40 GMT

Hi,

You can do this in script level .

Instead of Creating Inline table your script you can do this way

if(num(month(CalenderDate)) >6,num(month(CalenderDate))-6,num(month(CalenderDate))+6) as FiscalMonth

Regards,

Re: How to write the expression ?

suvechha_b — Tue, 25 Mar 2014 09:44:41 GMT

I am using this expression :

SUM({<[fis_period] = {'$(=NUM(Month(AddMonths(Today(),-1))))'}>}[Stag Forecast Units])

the following exp :'$(=NUM(Month(AddMonths(Today(),-1))))' is giving value 2

but Instead of 2 I want 8 .

Thus, I have declare :

MonthPeriod:

Load * inline [

Fiscal Month,Period

Jul,1

Aug,2

Sep,3

Oct,4

Nov,5

Dec,6

Jan,7

Feb,8

Mar,9

Apr,10

May,11

Jun,12

];

But how to write the expression to get the value of :'$(=NUM(Month(AddMonths(Today(),-1))))' as 8 insteat of 2.

Re: How to write the expression ?

MK_QSL — Tue, 25 Mar 2014 09:45:31 GMT

Or try this.. this should work..

=SUM({<[fis_period] = {"$(=Month(AddMonths(Today(),-1)))"}>}[Stag Forecast Units])

Re: How to write the expression ?

suvechha_b — Tue, 25 Mar 2014 09:56:27 GMT

=SUM({<[Fiscal Month] = {"$(=Month(AddMonths(Today(),-1)))"}>}[Stag Forecast Units])

How I can use the following expression instead of above :

=SUM({<[Period] = {8}>}[Stag Forecast Units])

as [Stag Forecast Units] field doesnot depend on [Fiscal Month] but depends on [Period] i.e ex , 8 for month feb

Re: How to write the expression ?

MK_QSL — Tue, 25 Mar 2014 09:59:26 GMT

Can you load your sample file or sample data along with your requirements?

Re: How to write the expression ?

Tue, 25 Mar 2014 10:15:22 GMT

Hi,

Try this

Create a variable

vPrevMonth= NUM(Month(AddMonths(Today(),6-1)))

SUM({<[fis_period] = {$(vPrevMonth)}>}[Stag Forecast Units])

=SUM({<[fis_period] = {'$(=NUM(Month(AddMonths(Today(),6-1))))'}>}[Stag Forecast Units])

Note: Differance in your calender and our calender

your calender starts from Jul

Re: Re: How to write the expression ?

suvechha_b — Tue, 25 Mar 2014 11:03:47 GMT

See the field sample .

SUM({<[fis_period] = {'$(=NUM(Month(AddMonths(Today(),-1))))'}>}[Stag Forecast Units])

the above expression is giving value of fis_period = 2

but I want fis_period as 8 thus I have declare in Inline Tab :

MonthPeriod:

Load * inline [

Fiscal Month,Period

Jul,1

Aug,2

Sep,3

Oct,4

Nov,5

Dec,6

Jan,7

Feb,8

Mar,9

Apr,10

May,11

Jun,12

];

feb, 8

PFA.

Re: How to write the expression ?

PrashantSangle — Tue, 25 Mar 2014 11:40:59 GMT

Hi,

Go to vw_fact_dop_forecast_stagger table

rename fis_period field as Period

fis_period field as [Fiscal Period]

So that You can create Link between vw_fact_dop_forecast_stagger and Your Inline Table

Then Use Your Expression,

It will work correctly,

Regards,