topic Re: Only function in an element set in set analysis? in QlikView

Only function in an element set in set analysis?

dena_reavis — Wed, 29 Mar 2017 16:51:10 GMT

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

];

Re: Only function in an element set in set analysis?

sunny_talwar — Wed, 29 Mar 2017 17:00:16 GMT

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)

Re: Only function in an element set in set analysis?

Gysbert_Wassenaar — Wed, 29 Mar 2017 17:05:57 GMT

=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)

Re: Only function in an element set in set analysis?

dena_reavis — Wed, 29 Mar 2017 17:17:54 GMT

Thank you so much, Gysbert!!!

Re: Only function in an element set in set analysis?

dena_reavis — Wed, 29 Mar 2017 17:36:46 GMT

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.

Re: Only function in an element set in set analysis?

sunny_talwar — Wed, 29 Mar 2017 17:39:51 GMT

we are glad we were able to help

Re: Only function in an element set in set analysis?

dena_reavis — Thu, 08 Jun 2017 18:53:03 GMT

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?

Re: Only function in an element set in set analysis?

Anil_Babu_Samineni — Fri, 09 Jun 2017 00:39:04 GMT

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 ?

Re: Only function in an element set in set analysis?

dena_reavis — Fri, 09 Jun 2017 12:46:04 GMT

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.

Re: Only function in an element set in set analysis?

dena_reavis — Fri, 09 Jun 2017 12:49:40 GMT

this is the Test file I meant to upload.

Sorry.

Re: Only function in an element set in set analysis?

dena_reavis — Fri, 09 Jun 2017 12:59:59 GMT

Its Above!

Thank you for everyone's help. (I feel like such a dunce but still grateful to the community.)

thank you,

Dena

Re: Only function in an element set in set analysis?

sunny_talwar — Sat, 10 Jun 2017 10:48:35 GMT

Try this

If(RowNo(TOTAL) > 3, RangeAvg(Above(TOTAL Only({<PayPeriodID, PayPeriodNumber>} Value), 0, 4)))