Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinRamsey
Creator
Creator

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
JustinRamsey
Creator
Creator
Author

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

rubenmarin

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)

JustinRamsey
Creator
Creator
Author

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.

sunny_talwar

Can you try this

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

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

sunny_talwar

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

JustinRamsey
Creator
Creator
Author

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

sunny_talwar

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

JustinRamsey
Creator
Creator
Author

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.