Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ">"
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)
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
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
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 ?
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
Hi All,
Here is my file
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
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 |
Rashmi
The expression gives 131092 = 130622 (August) + 470 (September). This look s correct to me. Or else you mean something completely different.
Jonathan