6 Replies Latest reply: Dec 10, 2015 12:00 AM by Kumar Pramod

Set analysis expression

Hi all,

I have a Inline script like below,

No_of_days_in_monthPeriod:

Monthperiods , no_of_days

Dec-15,28

Jan-16,35

Feb-16,28

Mar-16,28

Apr-16,28

May-16,35

Jun-16,28

Jul-16,35

Aug-16,28,

];

I need to get the no_of_days for particular Monthperiods  using set analysis,

i wrote set analysis like below but not working,

If(match(MonthPeriods={"\$(=max(MonthPeriod1))"},no_of_days))

max(MonthPeriod1) = 'Dec-15'

I need the value of no_of_days when max(MonthPeriod1)= MonthPeriods.

Can anyone explain and help where i am doing wrong

Thanks,

Kumar

• Re: Set analysis expression

In a text box:

=FirstSortedValue(no_of_days, -date#(Monthperiods, 'MMM-YY'))

and if there are multiple data points for max period, try with sum() like:

=Sum({<Monthperiods={"\$(=Date(max(date#(Monthperiods, 'MMM-YY')),'MMM-YY'))"}>}no_of_days)

Note, the date#() might not be required if your period is having proper date values at the backend, i.e.- it is a dual.

• Re: Set analysis expression

Hi Tresco,

Actually i am using one set expression like below:

To Calculate Projected use for current month period.

((count({\$<MonthPeriod={"\$(=Month(Today()))"}, DateType= {'accessed'}>} invitation_id)

/

count({\$<MonthPeriod={"\$(=max(MonthPeriod))"}>}Distinct CanonicalDate)-1)

*

28)

I need the value when, MonthPeriods = max(MonthPeriod)

For example now max(MonthPeriod) = Dec-15

next month it will change to Jan-16

So at that i need the value of no_of_days of Jan-16.

I think u understand my Scenario.

• Re: Set analysis expression

Your date-period field in the sample is a text field. So you have to parse the field to get the proper max value. Try the set comparison part as I showed above, like:

{<Monthperiods={"\$(=Date(max(date#(Monthperiods, 'MMM-YY')),'MMM-YY'))"}>}

If this doesn't work, try to share your sample qvw.

• Re: Set analysis expression

Sorry,

I understood the scenario,

In My Sample Monthperiods is a text field.

I have one more field called Monthperiod1,

Monthperiod1 is not a text field.

FYI the Qvf file doesnot contains recent dates data.

• Re: Set analysis expression

Try,

((count({\$<MonthPeriod={"\$(=Date(Today(),'MMM-YY'))"}, DateType= {'accessed'}>} invitation_id)

/

count({\$<MonthPeriod={"\$(=Date(Min(Date#(MonthPeriod,'MMM-YY')),'MMM-YY'))"}>}Distinct CanonicalDate)-1)

*

28)

• Re: Set analysis expression

HI Nagraj,

In above set expression, in the place of 28 i need to write a expression where it should pic dynamically value from the table.

So for that i am finding a expression.

For Jan-16 it should take 35 instead of 28.

Monthperiods , no_of_days

Dec-15,28

Jan-16,35

Feb-16,28

Mar-16,28

Apr-16,28

May-16,35

Jun-16,28

Jul-16,35

Aug-16,28,

];