Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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/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 !
Not sure, but this seems to be working for your sample
=Sum(If(Year(EndDate + 1) > Year(StartDate), Measure))
Hello Omar,
check the qvw.
Is that what you are looking for?
regards
tim
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)
It must be done without the script ! Thans Pradosh
I don't have qlikview right now, can u please describe what u've done?
try this Omar
SUM({<StartDate={"=(EndDate>yearend(StartDate))"}>}Measure)
Yearend is another field in your database?
No, It's calculated in the table : yearend(StartDate)
Oh okay... so what pradosh_thakur gave is working for you?
kind of... if it's a mock data, it works fine; but with the actual one, it has some weird behaviour...