topic Re: Set Analysis Comparing Two Dates in QlikView

Set Analysis Comparing Two Dates

Anonymous — Tue, 31 Jan 2017 18:43:43 GMT

Basically, I only want to SUM the $$ [Upsell ARR (converted)] if the Close Date of an Opp is > the start of a campaign. I feel like I have the right syntax below but it isnt working. Maybe I am overlooking something. Any help is appreciated.

Sum({<[Close Date] = {'>[Campaign Start Date]'}>}[Upsell ARR (converted)])

-dave

Re: Set Analysis Comparing Two Dates

vishsaggi — Tue, 31 Jan 2017 19:06:33 GMT

Is your Close Date and Campaign Start Date, holds same date formatting?

Re: Set Analysis Comparing Two Dates

sunny_talwar — Tue, 31 Jan 2017 19:15:02 GMT

May be this:

Sum(If([Close Date] > [Campaign Start Date], [Upsell ARR (converted)]))

Re: Set Analysis Comparing Two Dates

Anonymous — Tue, 31 Jan 2017 19:19:16 GMT

This worked thank you Sunny

Re: Set Analysis Comparing Two Dates

sunny_talwar — Tue, 31 Jan 2017 19:21:54 GMT

A even better approach here would be to create a flag in the script

LOAD If([Close Date] > [Campaign Start Date], 1, 0) as Flag

and then this

Sum({<Flag = {1}>} [Upsell ARR (converted)])

Re: Set Analysis Comparing Two Dates

Anonymous — Tue, 31 Jan 2017 19:41:04 GMT

Campaign Start Date and Close Dates aren't static so they need to be calculated per user selections, but thank you for the tip!!!