topic Re: Set Analysis Help Needed in QlikView

Set Analysis Help Needed

JustinRamsey — Wed, 25 Nov 2020 16:16:04 GMT

So I have an issue with a set analysis formula that I cannot figure out what is wrong with it. I am trying to have the formula display the value of (AFPO.WEWRT/AFPO.PSMNG) where the ProductionStartMonth (formatted as a date MM-YYYY) is the maximum which would display the newest price per unit.

The formula I came up with is MAX({<ProductionStartMonth=MAX(ProductionStartMonth)>} (AFPO.WEWRT/AFPO.PSMNG)) but that isnt working. The error message starting after the }. What am I missing with this?

Re: Set Analysis Help Needed

JustinRamsey — Thu, 06 Jun 2019 18:14:15 GMT

If the date could be causing the issue I could change it to the number AFPO.AUFNR but I would still need the max of that value

Re: Set Analysis Help Needed

rubenmarin — Thu, 06 Jun 2019 20:44:55 GMT

Hi, mainly the syntax for se analysis needs the values between brackets, also to evaluate max value inside brackets $-expansion can be used:

MAX({<ProductionStartMonth={$(=MAX(ProductionStartMonth))}>} (AFPO.WEWRT/AFPO.PSMNG))

Maybe you need to set date format:

MAX({<ProductionStartMonth={$(=Date(MAX(ProductionStartMonth), MM-YYYY))}>} (AFPO.WEWRT/AFPO.PSMNG))

This can work if WEWRT and PSMNG only has one value per ProducctionStartMonth, if there can be many values you have to aggregate them:

Sum({<ProductionStartMonth={$(=Date(MAX(ProductionStartMonth), MM-YYYY))}>} AFPO.WEWRT) /Sum({<ProductionStartMonth={$(=Date(MAX(ProductionStartMonth), MM-YYYY))}>}AFPO.PSMNG)

Re: Set Analysis Help Needed

JustinRamsey — Fri, 07 Jun 2019 12:28:39 GMT

Ive tried your formulas and I cant get them to work either. I have reconfigured my load script to give me a Production Year value (ProductionYear) and can get close to the value I want by using the function:

AVG({<ProductionYear={2019}>} (AFPO.WEWRT/AFPO.PSMNG))

This would return the average price per unit for all 2019 production orders. It's close to what I am wanting but I would prefer for it to display the value of the most recent Production order. My load script for the production day is: date(monthstart(AFPO.STRMP), 'MM-DD-YYYY') as ProductionStartDay,

date(monthstart(AFPO.STRMP), 'MM-YYYY') as ProductionStartMonth,
date(monthstart(AFPO.STRMP), 'YYYY') as ProductionYear,

Something is not working right in the section for the max date selection. I am lost as to why as it seems like it should work.

Re: Set Analysis Help Needed

sunny_talwar — Fri, 07 Jun 2019 12:34:23 GMT

Can you try this

Max({<ProductionStartMonth = {"$(=Date(Max(ProductionStartMonth), 'MM-YYYY'))"}>} AFPO.WEWRT/AFPO.PSMNG)

Re: Set Analysis Help Needed

JustinRamsey — Fri, 07 Jun 2019 13:36:11 GMT

Returned a value for only 1 line out of the 300 or so.

Re: Set Analysis Help Needed

sunny_talwar — Fri, 07 Jun 2019 13:49:38 GMT

Okay so what is the issue? May be you only have one row for Max ProductionStartMonth....

Re: Set Analysis Help Needed

JustinRamsey — Fri, 07 Jun 2019 13:51:11 GMT

There is approximately 300 parts that have a production date therefore they should all have a production max date.

Re: Set Analysis Help Needed

sunny_talwar — Fri, 07 Jun 2019 14:10:36 GMT

I am not sure I understand... are you able to share some sample data and the output you expect to see from it?

Re: Set Analysis Help Needed

JustinRamsey — Fri, 07 Jun 2019 14:25:53 GMT

See data table above. Trying to add column that displays the price per part on the most recent production order. All parts shown that have a production price should have a max production date that would be the most recent production order.

Re: Set Analysis Help Needed

sunny_talwar — Fri, 07 Jun 2019 15:22:40 GMT

Seems like what you really need is a FirstSortedValue() function instead of set analysis....

Re: Set Analysis Help Needed

JustinRamsey — Fri, 07 Jun 2019 17:05:10 GMT

I've never used that function. Can you assist me with its syntax? Tried

FirstSortedValue(AFPO.AUFNR, AFPO.STRMP, 1) as NewestOrder,

Where AFPO.AUFNR is the Production order number. but it errors out.

Re: Set Analysis Help Needed

sunny_talwar — Fri, 07 Jun 2019 17:59:32 GMT

You need to do this in the script?

Re: Set Analysis Help Needed

JustinRamsey — Fri, 07 Jun 2019 18:20:29 GMT

I dont need to I thought that was where the function was usually implemented per the examples I saw.

Re: Set Analysis Help Needed

sunny_talwar — Fri, 07 Jun 2019 18:24:46 GMT

Can be implemented on the front end as well. firstsortedvalue

I don't think I can give you the exact expression without testing it out based on your dimension and expectation, but feel free to try it out.

Re: Set Analysis Help Needed

JustinRamsey — Fri, 07 Jun 2019 19:32:52 GMT

Appears this may be working. Have to have a coworker verify in the system.

FirstSortedValue((AFPO.WEWRT/AFPO.PSMNG), -AFPO.STRMP, 1)