Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Set Analysis Help Needed

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?

15 Replies
Highlighted
Contributor III
Contributor III

Re: Set Analysis Help Needed

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

Highlighted

Re: Set Analysis Help Needed

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)

Highlighted
Contributor III
Contributor III

Re: Set Analysis Help Needed

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.

Highlighted

Re: Set Analysis Help Needed

Can you try this

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

Re: Set Analysis Help Needed

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

Highlighted

Re: Set Analysis Help Needed

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

Highlighted
Contributor III
Contributor III

Re: Set Analysis Help Needed

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

Highlighted

Re: Set Analysis Help Needed

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

Highlighted
Contributor III
Contributor III

Re: Set Analysis Help Needed

Example.PNG

 

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.