Count(Outlet) where the outlet is not closed.

[#1]

Hi All,

Need helps..

I need to create a straight table and the information as below

Dimension: Month

vMinDate: =monthstart(min(Report_DT))

Month	Outlet Count
Jan	sum({<Closed Date={'>=$(vMinDate)'}>} OutletCount)
Feb
Mar
Apr
May
Jun
July
Aug
Sep
Oct
Nov
Dec

The vMinDate actually will give me 01/Jan/2013 for all the months. Any expression I can do where it will check the closed date is >= the dimension month?

Or any other methods can give me the same result as what I need?

Regards,

Cs khoo

[its_anandrjs]

Do you share any sample data for this

Thanks & Regards

[#3]

sum({<Closed Date={'>=addmonths($(vMinDate),1)'}>} OutletCount) for Feb

sum({<Closed Date={'>=addmonths($(vMinDate),2)'}>} OutletCount) for March etc etc

[#4]

thanks. Got some idea from your statement.

Anyway, I created 12 variables contain..

vJan= makedate(only(Year),1)

vFeb = makedate(only(Year),2)

Formula to apply in the table is

if(month='Jan', sum({<Closed Date={'>=$(vJan)'}>}OutletCount),

....

....

...

[SunilChauhan]

sum({<[Closed Date]={">=$(=Min(ClosedDate))"}>}OutletCount)

try this it will take you through.

i am assuming here you have calclated Vmindate based on closed date.

if not please date used in VMindate

hope this helps

[VishalWaghole]

Hello Cs,

First create FLAG at Edit Script Level like,

if(isNull(YourCloasedDateField),'OPEN','CLOSED') as FLAG

then at expression level use this expression,

sum({<FLAG={'OPEN'}>} OutletCount)

this will help you.

-Regards,

Vishal Waghole

[#7]

It cannot create at script level because it need to dynamically change based on the date selected by users.

[VishalWaghole]

sum({<FLAG={'OPEN'}, [ClosedDate]={">=$(=Min(ClosedDate))"}>} OutletCount)

Please try this.

[#9]

Thanks, anyway, I already solved my problem....

[adiarnon]

you can do -

sum({<Closed Date={'>=addmonths($(vMinDate),num(column1))'}>} OutletCount)

and it will work on the whole table