Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
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)
Highlighted
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.