topic Re: Values between Start and Finish date in QlikView

Values between Start and Finish date

mohammadkhatimi — Thu, 21 Sep 2017 12:45:07 GMT

Hi,

My requirement is to capture count of days (week-wise) between Start and Finish date rest will be blank

Below is the output for reference:

Application and data is attached.

Please do the needful ASAP

Thanks in Advance,

Regards,

Mohammad

Re: Values between Start and Finish date

prieper — Thu, 21 Sep 2017 14:55:08 GMT

not clear on the required outcome:

Do you wish to know the days btw both dates? and then divide by 7? what to do with decimals?

Or do you wish to know the number of weeks elapsed betw the events?

Re: Values between Start and Finish date

mohammadkhatimi — Fri, 22 Sep 2017 06:11:35 GMT

I want to allocate days on the basis of Start and Finish date and respectively it should allocate to the standard weeks.

For desired result, look into the attachment.

Re: Values between Start and Finish date

mohammadkhatimi — Fri, 22 Sep 2017 09:26:01 GMT

gwassenaar‌

Plz help on this

Re: Values between Start and Finish date

mohammadkhatimi — Fri, 22 Sep 2017 11:21:52 GMT

stalwar1‌

plz help in this issue

Re: Values between Start and Finish date

Gysbert_Wassenaar — Fri, 22 Sep 2017 11:26:59 GMT

See attached qvw

Re: Values between Start and Finish date

sunny_talwar — Fri, 22 Sep 2017 11:46:13 GMT

Can you see if this works

=If(Weekend >= Start and WeekStart <= Finish,

If(Round(Finish - WeekStart) <= 7, Round(Finish - WeekStart), Round(Weekend - Start)) - If(Round(Finish - WeekStart) <= 7, 0, RangeMax(Before(TOTAL Round(Weekend - Start)), 0)))

Re: Values between Start and Finish date

mohammadkhatimi — Fri, 22 Sep 2017 12:35:51 GMT

Can explain the logic plz...

Re: Values between Start and Finish date

sunny_talwar — Fri, 22 Sep 2017 12:39:39 GMT

Sorry for not being very helpful... but if you really want to understand, you can look at the values generated by each part of the expression to see what they show and why I used them

1) Weekend >= Start and WeekStart <= Finish

2) Round(Finish - WeekStart)

3) Round(Finish - WeekStart)

4) Round(Weekend - Start)

5) RangeMax(Before(TOTAL Round(Weekend - Start)), 0)

and then build it from there.... that is exactly what I did

Re: Values between Start and Finish date

mohammadkhatimi — Fri, 22 Sep 2017 12:59:06 GMT

yeah ur rite..i cn understand by this way..bt my concern was to understand ( - ) minus sign what indicate between two if condition

Re: Values between Start and Finish date

sunny_talwar — Fri, 22 Sep 2017 13:12:04 GMT

This -> Round(Finish - WeekStart) generates a number which can exceed 7, so in order to get the week, I subtracted the days in previous week... there might be able another smart way to do this... but in the short spam of time I got, I came up with this

Re: Values between Start and Finish date

mohammadkhatimi — Sat, 23 Sep 2017 05:24:47 GMT

Ohh Okay...

Thank You so much for ur efforts.

Re: Values between Start and Finish date

mohammadkhatimi — Mon, 25 Sep 2017 09:40:37 GMT

Thanks..!!

Re: Values between Start and Finish date

mohammadkhatimi — Thu, 28 Sep 2017 09:22:54 GMT

Hi,

Everthing is working fine except for the calculation on last no.of days.

It should be Weekend-Finish, and with this change if the Finish date and weekend date is same then it gives me 1.

Please help me to resolve this.

Re: Values between Start and Finish date

mohammadkhatimi — Thu, 28 Sep 2017 09:46:20 GMT

stalwar1‌ kindly look into this..

Re: Values between Start and Finish date

sunny_talwar — Thu, 28 Sep 2017 11:11:08 GMT

Would you be able to pin-point in the application where the problem is?

Re: Values between Start and Finish date

mohammadkhatimi — Thu, 28 Sep 2017 12:10:58 GMT

As u select "Acti" as "Concept" in that weekend and finish date is same..

Re: Values between Start and Finish date

sunny_talwar — Thu, 28 Sep 2017 12:23:51 GMT

So instead of seeing a 6, you want to see 1?

Re: Values between Start and Finish date

mohammadkhatimi — Thu, 28 Sep 2017 12:59:28 GMT

To calculate the last no.of days, you used Finish-Weekstart.. it should be Weekend-Finish..to get the correct no. of days.

So, when I am using Weekend-Finish and if the finish date and weekend date is same then its showing 1..which should come as 7.Like wise you did it to calculate Start no.of days.

For.e.g Finish date is 17-10-2015 and weekend is also 17-10-2015.. So it took whole week to complete.. Therefore it should be 7.

Likewise if the date was 15-10-2015 and weekend is 17-10-2015, So the no.of days should be 3 days.

Hope you understand it.

Re: Values between Start and Finish date

sunny_talwar — Thu, 28 Sep 2017 17:15:55 GMT

If Finish = 17-10-2015 and weekend = 17-10-2015... and you do Weekend - Finish... you will get 0 (or 1 because you have timestamps)...

but if you use Finish = 17-10-2015 and WeekStart = 11-10-2015 and you do Finish - Weekend + 1... you might get what you are looking for

=If(Weekend >= Start and WeekStart <= Finish,

If(Round(Finish - WeekStart) <= 7, Round(Finish - WeekStart) + 1, Round(Weekend - Start)) - If(Round(Finish - WeekStart) <= 7, 0, RangeMax(Before(TOTAL Round(Weekend - Start)), 0)))