Use Where clause in LET

[dgmokati]

Hi All,

I want to use where clause in Let,

My requirement is to calculate count of distinct weeks for the latest quarter using global variable(LET).

Let vMaxWeek =count (distinct (quarter) where week =max('Week')

Please help me achieve this scenario .

Below is my sample data

Year	Quarter	Week
2013	20131	W1
2013	20131	W2
2013	20131	W3
2013	20131	W4
2013	20131	W1
2013	20131	W2
2013	20131	W3
2013	20131	W4
2013	20131	W1
2013	20131	W2
2013	20131	W3
2013	20131	W4
2013	20131	W5
2013	20132	W1
2013	20132	W2
2013	20132	W3
2013	20132	W4
2013	20132	W1
2013	20132	W2
2013	20132	W3
2013	20132	W4
2013	20132	W5
2013	20132	W1
2013	20132	W2
2013	20132	W3
2013	20132	W4

Kindly Help.

Thanks in Advance.

[Channa]

try to have IF (week =max(week), count())

[dwforest]

Have your logic backwards as it would count quarters and sadly there is no where in an aggregrate.

During load, if you used an if, it would process row by row, meaning the Max would always be as of the last record loaded.

As a variable, post load, you could use something like:

Count({<Quarter={"=max(TOTAL Quarter)"}> Distinct Week)