topic Re: Set Analysis Help in QlikView

Set Analysis Help

Mon, 09 Sep 2013 17:22:31 GMT

This code seems to not work. I am trying to grab the Max start date within the max end date. Is there something wrong with my syntax?

sum ({<rec_end_date={'$(=Max(rec_end_date))'}, {'(=Max(rec_start_date))'} <= date(vD_Date)>}[Actual Hrs]

This code below works but does not provide me with the one record I require.

sum ({<rec_end_date={'$(=Max(rec_end_date))'}, REC_START_DATE = {'<=$(=date(vD_Date))'}>}

Re: Set Analysis Help

Oleg_Troyansky — Mon, 09 Sep 2013 17:30:07 GMT

Forrest,

the syntax of the first expression is incorrect, and it's too long to explain what exactly is wrong... If you can formulate in simple IF questions what condition are you trying to formulate, I could try and help you do it with Set Analysis,

cheers,

Oleg Troyansky

Re: Set Analysis Help

Gysbert_Wassenaar — Mon, 09 Sep 2013 17:30:19 GMT

I'm not sure what you're trying to do. What certainly won't work is use an expression on the left side of the comparison. So {'(=Max(rec_start_date))'} <= date(vD_Date) is not going to work.

If you want the maximum startdate of the maximum enddate try:

sum ({<rec_end_date={'$(=Max(rec_end_date))'}, REC_START_DATE = {'$(=max(REC_START_DATE))'}>} SomeValue)

Make sure you use the exact case sensitive field names. For Qlikview rec_start_date is not the same as REC_START_DATE.

If this doesn't help please post a sample document with a more detailed explanation of what you trying to achieve.

Re: Set Analysis Help

Mon, 09 Sep 2013 17:51:51 GMT

Oleg,

I have a report that looking at a point in time to compare actuals and budget items as of a date provided by the users.

vD_Date = date selected by the user.

The way the table is created we have a point in time row for each actual and a row for each budget when it changes. Data sample below. Currently in other reports I can line up the budget and the actuals for current items by using set analysis to look at the Max rec_end_date. I now need to look at data based off of a as of date. So as of 1/22/11 what were the actual and budget for funding record 2.

Funding Record Actual Hours Budget Hours rec_start_date rec_end_date

2 5 1/1/11 1/25/11

2 2 1/2/11 1/10/11

2 5 1/11/11 1/1/9999

2 10 1/26/11 1/1/9999

Re: Set Analysis Help

Oleg_Troyansky — Mon, 09 Sep 2013 19:09:20 GMT

So, it looks like you are looking for the Maximum start date with the End Date greater than the requested vD_Date. Then, you need to use a nested Set Analysis condition:

sum ({<rec_start_date={'$(=Max({<rec_end_date = {"=num(rec_end_date)<=num($(vD_Date))"} >} rec_start_date))'} >} SomeValue)

Can't guarantee the exact syntax, I typed the formula directly here. The key is to use the condition for the end date as an inner Set Analysis condition within the condition for the start date. Each search condition requires a pair of either single or double quotes, and they need to be alternated.

best,

Oleg

Re: Set Analysis Help

Mon, 09 Sep 2013 19:44:53 GMT

Oleg,

Thank you for the help. It did not work the way I was expecting and in my infinite amount of time I have spent on this stupid thing I figured out my mistake. By not Summing but instead doing a Max it gives me the correct set analysis I was looking for. So by taking out the Max rec_end_date and then just grabbing the Max value based off of the rec_start_date it is grabbing what I am looking for.

Max ({<REC_START_DATE = {'<=$(=Date(vD_Date))'}>}[Actual Hrs])

Re: Set Analysis Help

Oleg_Troyansky — Mon, 09 Sep 2013 20:16:12 GMT

wait, I don't think this is right... The MAX function applies to the MAX hours, not MAX date. SO, if you should have a number of records, all of them before the D-date, then you will be grabbing the highest number of hours, not the hours for the highest starting date.

Perhaps you want to use the function FirstSortedValue instead? This way, you can grab the first (or the last) available record, not necessarily the highest number of hours?