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: 
Not applicable

Help with Set Analysis

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]))

1 Solution

Accepted Solutions
Not applicable
Author

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])

View solution in original post

7 Replies
phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

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.

Not applicable
Author

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.

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

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.

Not applicable
Author

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

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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])

-

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]))

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

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])