topic Re: WTD_PRVWTD in QlikView

WTD_PRVWTD

karthikoffi27se — Wed, 25 Nov 2020 16:16:04 GMT

I am currently finding the WTD and Previous year WTD.

I am using the formula as mentioned below

Current Year WTD:

Sum({<ISSUEDT_YEAR=, ISSUEDT_MONTH=, ISSUEDT=, DateKey={">=$(=Num(WeekStart(Max(DateKey)))) <=$(=Max(DateKey))"}>}PREMIUM)

Previous Year WTD:

Sum({<ISSUEDT_YEAR=, ISSUEDT_MONTH=, ISSUEDT=,DateKey={">=$(=Num(WeekStart(Max(DateKey), -52)))<=$(=WeekEnd(AddYears(Max(DateKey), -1)))"}>} PREMIUM)

My concern is we are changing the weekstart into numerical format but we are leaving the weekend to calculate as date format, how is the formula picking up.

Previous Year WTD is calculating current year premium. Is there anything I need to change in the formula.

Many Thanks

Karthik

Re: WTD_PRVWTD

siddharth_s3 — Tue, 27 Feb 2018 10:10:12 GMT

I don't think so.

In the backend, dates are essentially numbers. The way date and time work in RDBMS or Qlik or Excel is, The fraction part is your date and the decimal part is your time.

For example, the time in India now is 43158.6506944444

Where 43158 is the date i.e. 02/27/2018 & the decimal part 6506944444 is the time which is 15:39.
So be it a number or a date format, it should not matter. It is just formatting.

Re: WTD_PRVWTD

sunny_talwar — Tue, 27 Feb 2018 10:10:47 GMT

May be try this

Sum({<ISSUEDT_YEAR=, ISSUEDT_MONTH=, ISSUEDT=, DateKey={">=$(=Num(WeekStart(AddYears(Max(DateKey), -1))))<=$(=Floor(WeekEnd(AddYears(Max(DateKey), -1))))"}>} PREMIUM)

Re: WTD_PRVWTD

Anonymous — Tue, 27 Feb 2018 12:14:56 GMT

Try below

Previous Year WTD:

Sum({<ISSUEDT_YEAR=, ISSUEDT_MONTH=, ISSUEDT=,DateKey={">=$(=Num(WeekStart(AddYears(Max(DateKey), -1))))<=$(=Num(WeekEnd(AddYears(Max(DateKey), -1))))"}>} PREMIUM)

Re: WTD_PRVWTD

pablolabbe — Tue, 27 Feb 2018 19:31:11 GMT

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others