topic Re: Re: Re: Set analysis to include end dates based on start date selected in QlikView

Set analysis to include end dates based on start date selected

charlotte_qvw — Thu, 13 Nov 2014 11:47:19 GMT

Hi, I'm looking for some set analysis help. I have ref and start and end date. I hold a row for each reference for each month, with a start flag against the month the reference started in and an end flag against the month the reference ended in. I'd like to select a month and display all the references that started in that month and in a different column, a 0 or a 1 depending on whether they've ended or not. Is this possible to do using the P in set analysis? if I don't use P, my end flag column also filters for the month selected.

I attach some data and an example qvw.

Many thanks, charlotte

Re: Set analysis to include end dates based on start date selected

Gysbert_Wassenaar — Thu, 13 Nov 2014 11:57:27 GMT

See attached qvw.

Re: Set analysis to include end dates based on start date selected

charlotte_qvw — Thu, 13 Nov 2014 12:15:32 GMT

Thanks Gysbert. I missed out some examples in my data where the end is in fact blank. So for all my examples, there should be a 1 in the end column, as they've all ended.

I attach a new example with a blank end date as well. what I would like, is when the use selects the April date, for the 4 April start references to be displayed. There should be a 1 in the start column for all, and a 1 in the end column for the 3 that have ended and a 0 for the one that has not ended.

Thanks again, appreciated !

Re: Re: Set analysis to include end dates based on start date selected

charlotte_qvw — Thu, 13 Nov 2014 12:17:02 GMT

Thanks Gysbert. I missed out some examples in my data where the end is in fact blank. So for all my examples, there should be a 1 in the end column, as they've all ended.

Thanks again, appreciated !

Re: Re: Re: Set analysis to include end dates based on start date selected

morganaaron — Thu, 13 Nov 2014 13:32:35 GMT

Hi Charlotte,

Assuming you only have an end date where something has ended, you could use something like this. I changed the expression totals to sum the rows so that you get a count of how many have started/ended in total for any month.

This doesn't use your 'end counter' as that didn't seem to correlate with your end dates - if you need it only to show where end counter = 1, you can use a similar idea to what was provided for the start counter.

Aaron