Dynamic Date in Set Analysis

[#1]

Hi All,

I trying to get total number of orders completed in last 4 weeks... I am using following expression

Count({$<[Order Status]={'Complete'}, [Product Category]={'Shoes'},[Completion Date]}>}OrderId)

where can I add that last 4 weeks clause in the above set analysis ?

Completion Date field name is [Completion Date]

[sunny_talwar]

May be like this:

Count({$<[Order Status] = {'Complete'}, [Product Category] = {'Shoes'}, [Completion Date] = {"$(='>=' & Date(Max([Completion Date]) - 28, 'DateFieldFormatHere') & '<=' & Date(Max([Completion Date]), 'DateFieldFormatHere'))"}>}OrderId)

[marjan_it]

you can use it:

=Count(if(Order Status='Complete' and Product Category='Shoes' and (Num(Week(date(now()))) - Num(Week(SubmitDocDate1))=4),OrderId))

[#4]

Hi Lokesh:

You can try this :

=Count(if(Order Status='Complete' and Product Category='Shoes' and (Num(Week(date(now()))) - Num(Week(SubmitDocDate1))=4),OrderId))

Thanks

Nandu

[jyothish8807]

Hi Sunny,

Just a bit curious to know why are you using ' ' and & while using >= in set analysis ?is there any particular reason  you are using this ?

{"$(='>=' & Date(Max([Completion Date]) - 28, 'DateFieldFormatHere')

Thanks a lot in advance. I have learned a lot from your solutions

Regards

KC

[Anonymous]

Hi Nanda, try this:

Count({$<[Order Status] = {'Complete'}, [Product Category] = {'Shoes'},

    [Completion Date] = {$(>=Date(MakeWeekDate(Year(WeekStart(Max([Completion Date])-28)), Week(WeekStart(Max([Completion Date])-28))),'DD/MM/YYYY'))}>}OrderId)

It will get first day of 4 weeks before.

Regards!!!

[Mark_Little]

Hi,

Things like this i would normally do in script as it helps with front end performance.

in you calendar add

IF(Date>= Today()-28 and Date<=Today(),1,0) As RollingMonth

Then you set analysis would be

Count({$<[Order Status]={'Complete'}, [Product Category]={'Shoes'},RollingMonth={1}}>}OrderId)

Mark

[sunny_talwar]

But mark, how would this be dynamic? Once set, flags will be fixed to 0 and 1 and won't change based on selections. Isn't it?

[Mark_Little]

Hi Sunny,

I guess it depends how he means, I took it to mean the prior 28 days, so on a nightly reload this would always show the last 28 days.

If he is wanting the 4 weeks prior to a selection then you're right, my approach is terrible.

Mark

[#10]

Thank you all for reply..

I have temporarily used if statement in my expression instead of set analysis.

Count(Distinct(if([Order Status] = 'Complete' AND [Product Category] = 'Shoes' AND [Completion Date] > Date(Today()-28), OrderId)))

I will try one of your Set Analysis Expression and mark the correct answer tomorrow

Thank you.