Only function in an element set in set analysis?

[dena_reavis]

Hello experts, please help me solve this problem, which is very  likely that I messed up the syntax...

The purpose of this is to dynamically label columns of pay period data, with the number of the pay period or name. There is only one current pay period at a time, which I am using to get the Pay Period ID.

This works for the current PayPeriodID, returns 272 for the PayPeriodID

=Only({$<CurrentPayPeriod={"1"}>} PayPeriodID)

But I want to label the column with a friendly pay period label for pay period 272, the PayPeriodNumber, and be able to get this for the previous pay periods. So I thought I could use the Only function in the element set. Maybe I can't do that or I have the syntax wrong. I can't figure it out.

=Only({$<PayPeriodID={"<=$(=Only({$<CurrentPayPeriod={"1"}>} PayPeriodID))"}>} PayPeriodNumber)

and I thought I could later subtract 1 from the PayPeriodID to get the previous pay period for each label. I am entering these as separate expressions on each label, of each expression. 

This is some of the data. You can see I can't just subtract 1 from the Pay Period Number because it wouldn't capture the pay period before Pay Period 1 and when prior pay periods are in a different fiscal year.  Here is an inline load of some of my data.

Thanks for any help!! By the way, if there's a better way to do this, please let me know. Thank you!!

LOAD * INLINE [

    CurrentPayPeriod, PayPeriodNumber, PayPeriodEndDate, PayPeriodID

    0, 25, 9/3/2016, 259

    0, 26, 9/17/2016, 260

    0, 1, 10/1/2016, 261

    0, 2, 10/15/2016, 262

    0, 3, 10/29/2016, 263

    0, 4, 11/12/2016, 264

    0, 5, 11/26/2016, 265

    0, 6, 12/10/2016, 266

    0, 7, 12/24/2016, 267

    0, 8, 1/7/2017, 268

    0, 9, 1/21/2017, 269

    0, 10, 2/4/2017, 270

    0, 11, 2/18/2017, 271

    1, 12, 3/4/2017, 272

    0, 13, 3/18/2017, 273

    0, 14, 4/1/2017, 274

];

[sunny_talwar]

I think double quotes under double quotes seems to be a problem, you can try this

=Only({$<PayPeriodID={"<=$(=Only({$<CurrentPayPeriod={1}>} PayPeriodID))"}>} PayPeriodNumber)

or this:

=Only({$<PayPeriodID = {"$(=Max({$<CurrentPayPeriod = {1}>} PayPeriodID))"}>} PayPeriodNumber)

[Gysbert_Wassenaar · Accepted solution]

=Only({$<PayPeriodID={"<=$(=Only({$<CurrentPayPeriod={"1"}>} PayPeriodID))"}>} PayPeriodNumber)

That won't work since the <= will select multiple PayPeriodID values and thus result in a set of multiple PayPeriodNumber values. The Only function will return Null if there are multiple values. Try:

Current period: =Only({$<PayPeriodID={"$(=Only({$<CurrentPayPeriod={1}>} PayPeriodID))"}>} PayPeriodNumber)

Previous period: =Only({$<PayPeriodID={"$(=Only({$<CurrentPayPeriod={1}>} PayPeriodID)-1)"}>} PayPeriodNumber)

[dena_reavis]

Thank you so much, Gysbert!!!

[dena_reavis]

And also thank you, Sunny Talwar! The second expression worked!

I think you have helped me before and I really appreciate it. Thank you very much.

D

[sunny_talwar]

we are glad we were able to help

[dena_reavis]

Hi,

I need help calculating an average for a number of pay periods backwards. I updated the data below to include a value that I'd like to average.

LOAD * INLINE [

    CurrentPayPeriod, PayPeriodNumber, PayPeriodEndDate, PayPeriodID, Value

    0, 25, 9/3/2016, 259, 4

    0, 26, 9/17/2016, 260, 5

    0, 1, 10/1/2016, 261, 6

    0, 2, 10/15/2016, 262, 3

    0, 3, 10/29/2016, 263,4

    0, 4, 11/12/2016, 264,7

    0, 5, 11/26/2016, 265,4

    0, 6, 12/10/2016, 266,5

    0, 7, 12/24/2016, 267,5

    0, 8, 1/7/2017, 268,4

    0, 9, 1/21/2017, 269,3

    0, 10, 2/4/2017, 270,6

    0, 11, 2/18/2017, 271,4

    1, 12, 3/4/2017, 272,3

    0, 13, 3/18/2017, 273,5

    0, 14, 4/1/2017, 274,4

];

In a set analysis how would I refer to the PayPeriodID and say 3 pay periods back.

I would start with avg( {$<PayPeriodID ={"268"}>} Value) if I just wanted the average of PP 268.

How do I refer to the PayPeriodID without naming it explicitly and 3 payperiods back, like 268, 267, 266, and 265?

[Anil_Babu_Samineni]

I am sorry, If i miss some thing to read. Perhaps this?

avg( {$<PayPeriodID -= {'268', '267', '266', '265'}>} Value)

Or Better, If you explain expected result set ?

[dena_reavis]

Hi,

Thank you for your reply. I have attached an example in Excel where I calculate the average of the current pay period and the three prior pay periods. Some might call this a rolling 4 pay period average. Perhaps "above" is the function to use, but I don't know. I also attached the test data loaded into a QVW.

I am truly grateful for your help!

Thank you.

[dena_reavis]

this is the Test file I meant to upload.

Sorry.