topic Re: Help with Set Analysis in Visualization and Usability

Help with Set Analysis

Mon, 25 Aug 2014 20:21:20 GMT

I have the following IF statement but the test always fails and I only get the "false" side of the expression on every use. I should, depending on the project's projected closing date, return a "True" but it only works when I select a project or projects. In other words, this statement always fails the first IF statement when no projects are selected. If I select a project that has a Proj Closing Date of next year the statement returns "True", If I don't select this project it is "False". Any help much appreciated.....Thanks

=IF(Year([Proj Closing Date])> Year(vMaxDate),

SUM({$<Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} IF(Year([Proj Closing Date])> Year(vMaxDate), [CY Fee Budget], [Fee Budget]* [Probability of Closing]))

SUM({$<Year= , Year ={"$(vMaxYear)"}, [Deal Status]= >} Revenue),

SUM({$<Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC","Won Pitch"}>} IF(Year([Proj Closing Date])> Year(vMaxDate), [CY Fee Budget], [Fee Budget]* [Probability of Closing]))

SUM({$<Year= , Month= , YearMonth= , Date= ,Date = {"<=$(vMaxDate)"}, [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} Revenue* [Probability of Closing]))

Re: Help with Set Analysis

phaneendra_kunc — Mon, 25 Aug 2014 20:52:39 GMT

i believe this to work you have to have Proj Closing Date as dimension in the chart. Because in qlikview if that field is not in grid/chart it wont do a row wise computation.

lets say you have 3 years in Proj Closing Date, behind the seen qlikview will be taking all 3.

In oyur case when you select one project you are getting one year only and that simplifies for IF and you are seeing the result.

Best example: Add "=Year(Proj Close Date)" in a text box and clear everything. you will see null "-". This is what is happening in your scenario when you dont select a project.

Can you try adding Proj Closing Date a dimension? or if you dont want you have to go with Aggr statement.

Re: Help with Set Analysis

Mon, 25 Aug 2014 21:00:17 GMT

I understand exactly what you're talking about. And yes the test for the chart does come back null. I do have the proj close date as a dimension in my chart and in fact each line in the table brings back the proper result of the expression. The problem I have is the expression as a total for the chart is incorrect. I just don't know how to get the expression in total to work.

Re: Help with Set Analysis

phaneendra_kunc — Mon, 25 Aug 2014 21:03:24 GMT

Can you share your QVF or QVW with me. I will try to fix it.

usually when you do TOTAL you have to specify what to sonsider in your Total Aggregation by "<>" including dimesions in those angle brackets.

Re: Help with Set Analysis

Mon, 25 Aug 2014 21:10:59 GMT

I wish I could but there is too much confidential data within.

Re: Help with Set Analysis

Mon, 25 Aug 2014 21:22:35 GMT

I still don't understand how to get the initial if statement to return True

Re: Help with Set Analysis

jagan — Tue, 26 Aug 2014 02:02:22 GMT

Hi Brian,

Try like this

=IF(Year(Max([Proj Closing Date]))> Year(vMaxDate),

SUM({$<Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} [CY Fee Budget])

SUM({$<Year= , Year ={"$(vMaxYear)"}, [Deal Status]= >} Revenue),

SUM({$<Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC","Won Pitch"}>} [Fee Budget]* [Probability of Closing])

Hope this helps you.

Regards,

Jagan.

Re: Help with Set Analysis

Tue, 26 Aug 2014 13:41:53 GMT

Thanks. I tried but came up with incorrect expression total again. Ended up modifying it to this and it appears to work:

SUM({$<[Proj Closing Date]={">2014-12-31"},Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} [CY Fee Budget]*[Probability of Closing])

SUM({$<[Proj Closing Date]={">2014-12-31"},Year= , Year ={"$(vMaxYear)"}, [Deal Status]= >} Revenue)

SUM({$<[Proj Closing Date]={"<=2014-12-31"},Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC","Won Pitch"}>} [Fee Budget]* [Probability of Closing])

SUM({$<[Proj Closing Date]={"<=2014-12-31"},Year= , Month= , YearMonth= , Date= ,Date = {"<=$(vMaxDate)"}, [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} Revenue* [Probability of Closing])