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: 
OmarBenSalem

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

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)

clientStartDateEndDateYearEnd(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 !

1 Solution

Accepted Solutions
sunny_talwar

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

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

Capture.PNG

View solution in original post

19 Replies
zhadrakas
Specialist II
Specialist II

Hello Omar,

check the qvw.

Is that what you are looking for?

regards

tim

pradosh_thakur
Master II
Master II

Hi omar

script like this

LOAD * ,IF(EndDate < YearEnd(StartDate),'NO','YES') AS FLAG;

LOAD * 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

];

DIMENSION

YEARSTART(StartDate)

EXPRESSION

SUM({<FLAG={'YES'}>}Measure)

Learning never stops.
OmarBenSalem
Author

It must be done without the script ! Thans Pradosh

OmarBenSalem
Author

I don't have qlikview right now, can u please describe what u've done?

pradosh_thakur
Master II
Master II

try this Omar


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

Learning never stops.
sunny_talwar

Yearend is another field in your database?

OmarBenSalem
Author

No, It's calculated in the table : yearend(StartDate)

sunny_talwar

Oh okay... so what pradosh_thakur‌ gave is working for you?

OmarBenSalem
Author

kind of... if it's a mock data, it works fine; but with the actual one, it has some weird behaviour...