Ignore date field (Year, Month, Week ) selections from chart but rest all selection should work

[#1]

Hi All,

I am trying to ignore only date selections for my chart. But rest selection criteria should work normally when i am selecting something.

I got an idea how to restrict one field in the chart by reading the blogs

count({$<[Month Name]=> }[Upgraded Installation Client Dim Key])

This work fine for Month Selection

But when i try to give more than one field it does not work. 😞

Here is the expression I am trying to use for multiple selection.

count({1<[Month Name]= P([Month Name]) , [Cal Week] = P([Cal Week]),[Cal Quarter] = P([Cal Quarter])> }[Upgraded Installation Client Dim Key])

Please guide me on this issue how can i ignore more than one selection in a chart.

Thanks,

Rashmi

[deepakk]

Variable syntax is different you have use $(variablename)

eg :

count

({<Reported_Date= {">=$(vChkFromDt"}>} distinct Number

)

where vchkFromDT is the variable

Apply it in similar method. For equal to case remove greater than sign ">"

[gandalfgray]

Sum({<[Ship Month]={">=$(=POS_Month)"}, [Ship Year]={"$(=POS_Year)"}, [Link Row WID]=, [W Cal Week]=, [Cal Quarter]=>} [Aggr Net Sales Qty]))

something like the above.

If POS_Month and POS_Year are variables you need to enclose them in $(= )

If you try to do a >= comparison you need to enclose the expression in doublequotes (may work with quotes also but I prefer doublequotes)

[#4]

Hi Jonathan,

I tried the all the possible ways you suggested but no luck 😞

Is there any other way .

LOAD

Sales_POS:

[Ship Month],

[Ship Year],

[Link POS] as [Aggr Install Link POS] ,

[Aggr Net Sales Qty]

FROM

[$(dashdataDir)\ENGINEERING\POS.qvd] (qvd) ;

Can I include this condition in script level here is my table where I want to apply this filter condition

[jonathandienst]

Rashmi

Can you post your model here, or a sample of it? That way it is easier to understand why the expression is not working. Click on the Options tab to upload.

Jonathan

[#6]

I am sorry Jonathan,the information was not complete actually my script is something like this

Sales_POS:

LOAD

[Ship Month],
[Ship Year],
[Link POS] as [Aggr Install Link POS],
[Aggr Net Sales Qty]
FROM

[$(dashdataDir)\ENGINEERING\POS.qvd] (qvd);

and I have to apply two filter conditions here

[Ship Month] >= =Month(Min([Max Install Fact SRC Insert Date]))
and
[Ship Year] =Year(Min([Max Install Fact SRC Insert Date]))

My table from where [Max Install Fact SRC Insert Date] is comming is like this

Install_Fact_Client:

Load

[Link Install Fact Client Key] ,
[Install Fact Key] as [Aggr Install Fact Key],
[Install Fact Server Date Key] as [Aggr Install Fact Server Date Key] ,
[Install Fact Client Dim Key] as [Aggr Install Fact Client Dim Key],
[Install Fact Current Version Key] as [Aggr Install Fact Current Version Key],
[Install Fact Previous Version Key] as [Aggr Install Fact Previous Version Key],
[Install Fact Current Lang Key] as [Aggr Install Fact Current Lang Key],
[Install Fact Previous Lang Key] as [Aggr Install Fact Previous Lang Key] ,
[Install Fact Current Country Key] as [Aggr Install Fact Current Country Key],
[Install Fact Previous Country Key] as [Aggr Install Fact Previous Country Key] ,
[Install Fact OS Lang Key] as [Aggr Install Fact OS Lang Key],
[Install Fact OS Country Key] as [Aggr Install Fact OS Country Key] ,
[Install Fact Client Timestamp] as [Aggr Install Fact Client Timestamp],
[Install Fact DW Insert Date] as [Aggr Install Fact DW Insert Date],
[Install Fact SRC Insert Date] as [Max Install Fact SRC Insert Date],
Month([Install Fact SRC Insert Date]) & '-' & Year([Install Fact SRC Insert Date]) as [Aggr Install Link POS]

Resident Install_Fact;

INNER JOIN
LOAD
[Link Client Dim Key] as [Aggr Install Fact Client Dim Key],
[Client ID] as [Aggr Client ID]

Resident Client_Dim;

how can I give filter conditions in Script ?

[jonathandienst]

Rashmi

Are you sure you want to filter in the script? If so, the following should work:

//AFTER loading Install_Fact_Client:
tmpDates:
LOAD
 Month(Min([Max Install Fact SRC Insert Date])) AS tmpMonth,
 Year(Min([Max Install Fact SRC Insert Date])) AS tmpYear
RESIDENT Install_Fact_Client;

Let vMonth = Peek('tmpMonth');
Let vYear = Peek('tmpYear');

Sales_POS:
LOAD
 [Ship Month],
 [Ship Year],
 [Link POS] as [Aggr Install Link POS],
 [Aggr Net Sales Qty]
FROM [$(dashdataDir)\ENGINEERING\POS.qvd] (qvd)
WHERE [Ship Month] > '$(vMonth)' AND [Ship Year] = $(vYear);

//... Assuming [Ship Month] is a text field

i still suggest that you post your model.

Jonathan

[#8]

Hi All,

Here is my file

[jonathandienst]

Rashmi

See attached solution.

The correct expression is

Sum({<[Ship Month]={">=$(=Num(POS_Month))"}, [Ship Year]={$(POS_Year)}, [Link Row WID]=, [W Cal Week]=, [Cal Quarter]=>} [Aggr Net Sales Qty])

Jonathan

[#10]

Hi ,

I tried the script you have givin Month and year values are calculating correctly

[Ship Month] is atext field but the where clause is ot applied to the table Here is the data for table

Aggr Install Link POS	Aggr Net Sales Qty	Ship Month	Ship Year
Aug-2010	130622	8	2010
Feb-2010	161946	2	2010
Jan-2010	166414	1	2010
Jul-2010	203217	7	2010
Jun-2010	203059	6	2010
Mar-2010	157923	3	2010
May-2010	231637	5	2010
Oct-2010
Sep-2010	470	9	2010

[jonathandienst]

Rashmi

The expression gives 131092 = 130622 (August) + 470 (September). This look s correct to me. Or else you mean something completely different.

Jonathan