WTD_PRVWTD

[karthikoffi27se]

Hi

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

[siddharth_s3]

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.

[sunny_talwar]

May be try this

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

[Anonymous]

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)

[pablolabbe]

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