Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
Try,
((count({$<MonthPeriod={"$(=Date(Today(),'MMM-YY'))"}, DateType= {'accessed'}>} invitation_id)
count({$<MonthPeriod={"$(=Date(Min(Date#(MonthPeriod,'MMM-YY')),'MMM-YY'))"}>}Distinct CanonicalDate)-1)
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.
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.
