Set analysis expression

[#1]

Hi all,

I have a Inline script like below,

No_of_days_in_monthPeriod:

LOAD * INLINE [

  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

[tresesco]

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.

[#3]

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.

[tresesco]

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'))"}>}

adjust similarly for today() comparison.

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

[tamilarasu]

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)

[#6]

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.

[#7]

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.

LOAD * INLINE [

  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,

  ];