Question for champions (can't find a right title to my question :D )

[OmarBenSalem]

Hi all, stalwar1‌

I have this scenario:

I have multiple clients :

let's say : 3 clients

each client has different start and end dates

what I want to do: if a yearEnd ( a calculated field) is btwn StartDate and EndDate, we take the Measure into consideration, else, we don't:

the red line are the good ones:

(till now, it's fine)

client	StartDate	EndDate	YearEnd(calculated = yearEnd(StartDate)	Measure
client1	04/05/2014 05/10/2014 01/05/2015 15/10/2015 20/04/2016	04/09/2014 05/02/2015 10/09/2015 15/01/2016 15/01/2017	31/12/2014 31/12/2014 31/12/2015 31/12/2015 31/12/2016	100 200 300 400 500
client2	10/01/2015 12/12/2015 01/02/2017 20/09/2017	05/08/2015 20/02/2016 10/05/2017 15/01/2018	31/12/2015 31/12/2015 31/12/2017 31/12/2017	100 200 300 400
client3	10/07/2015 15/01/2016	11/01/2016 15/01/2017	31/12/2015 31/12/2016	100 200

Now, what I want to do is create a new bar chart as follow:

as dimension: year  (probably, year(StartDate) ?

as a measure : My Measure

and it would be as follow:

2014 : 200 (the first red of client1)

2015 : 400 (2nd red for client1) + 200 (1st red for client2) + 100 (first red for client3)

2016 : 500 (3rd red for client1) + 200 (2nd red of client3)

2017 ; 400 (2nd red for client2)

How can I do this?

thanks !

[sunny_talwar · Accepted solution]

Not sure, but this seems to be working for your sample

=Sum(If(Year(EndDate + 1) > Year(StartDate), Measure))

[OmarBenSalem]

I'll do some tests on my actual dta, to see why this does not work.. I already tried ur suggested expressions and it did not do the trick..

If I find any clue , I'll keep u in touch.

Thanks for all of u !

[kaanerisen]

Hi Omar,

I guess you have to assign a unique id to all rows.

Script:

test:

LOAD AutoNumber(Client&StartDate&EndDate&Measure) as id,* INLINE [

    Client, StartDate, EndDate, Measure

    client1, 4/5/2014, 4/9/2014, 100

    client1, 5/10/2014, 5/2/2015, 200

    client1, 1/5/2015, 10/9/2015, 300

    client1, 15/10/2015, 15/01/2016, 400

    client1, 20/04/2016, 15/01/2017, 500

    client2, 10/1/2015, 5/8/2015, 100

    client2, 12/12/2015, 20/02/2016, 200

    client2, 1/2/2017, 10/5/2017, 300

    client2, 20/09/2017, 15/01/2018, 400

    client3, 10/7/2015, 11/1/2016, 100

    client3, 15/01/2016, 15/01/2017, 200

];

Bar Chart Expressions:

Dimensions 1:

year(date#(StartDate,'DD/MM/YYYY'))

Dimension 2:

Client

Measure:

Sum({<id={"=date#(EndDate,'DD/MM/YYYY')>yearend(date#(StartDate,'DD/MM/YYYY'))"}>}Measure)

Hope İt helps...

[pradosh_thakur]

if possible just paste your expression Omar. Lets find out if there is anything you need to change from the expression itself. Data won't be needed i guess.

[sunny_talwar]

So even this new expression doesn't work for your actual data?

[OmarBenSalem]

I'm really sorry for the late answer, I was on my launch break

The answer of sunny did work on my actual data set :

=Sum(If(Year(EndDate + 1) > Year(StartDate), Measure))

while this:

SUM({<StartDate={"=(EndDate>yearend(StartDate))"}>}Measure)

did not (and it was my first try also)

stalwar1‌, can u explain why? why qlik interpets the 2 expressions differently?

[OmarBenSalem]

every contribution is helpful !

[sunny_talwar]

The expression that pradosh_thakur‌ provided looks at a table where StartDate is a dimension and checks if EndDate >YearEnd(StartDate)... the problem is that you might have a single StartDate associated with more than one EndDate.... that is where is fails... if this was always one-to-one relation... it would have worked....

My expression is just doing a row level evaluation of each and every single StartDate and EndDate.... If you were to have a UniqueKey, you could have used this

=Sum({<UniqueKey = {"=Year(EndDate + 1) > Year(StartDate)"}>} Measure)

Alternatively, Pradosh's response might work like this

Sum(If(EndDate > YearEnd(StartDate), Measure))

[OmarBenSalem]

very well explained ! thanks !

[pradosh_thakur]

yeah!!! Never thought of the one to many connection between dates while writing expression . Will keep it in mind from now.