topic Re: Last 12 months in QlikView

Last 12 months

Zaga_69 — Sat, 16 Nov 2024 05:04:50 GMT

Hi everyone!

I need to calculate the last twelve months for the following formula:

Sum({<DOT_STATUS={'Too Early'},PlannedGoodsMovPeriod={"$(=max(YearMonth))"}>}YCOUNT)

I have tried using addmonths function in the formula, but ii does not work.

Any hint?

Thankns

Edi

Re: Last 12 months

sunny_talwar — Mon, 03 Dec 2018 13:50:21 GMT

You have two different Date related field? PlannedGoodsMovPeriod and YearMonth? and you want to see last 12 months of PlannedGoodsMovPeriod by selecting in YearMonth? How are these two fields connected? What is the format for PlannedGoodsMovPeriod field?

Re: Last 12 months

Zaga_69 — Mon, 03 Dec 2018 13:56:36 GMT

you want to see last 12 months of PlannedGoodsMovPeriod by selecting in YearMonth?

Yes. I wanted to sum the field YCOUNT for the last 12 months (i.e YearMonth -12)

Format:

date(date#(YWADAT,'YYYYMMDD'),'YYYYMM')&'' AS PlannedGoodsMovPeriod,

date(date#("0DATE",'YYYYMMDD'),'YYYYMM')&'' AS ActualGoodsMovPeriod,

The field YearMonth was created in the Calendar:

Year(TempDate)&date(TempDate,'MM') As YearMonth,

Thanks in advance,

Edi

Re: Last 12 months

sunny_talwar — Mon, 03 Dec 2018 14:11:38 GMT

Out of curiosity why do you have & '' at end of PlannedGoodsMovPeriod and ActualGoodsMovPeriod? That converts a date field into text, is that something you really want to do for reason not mentioned in this thread?

Re: Last 12 months

Zaga_69 — Mon, 03 Dec 2018 14:18:54 GMT

because it was the only solution I could found to calculate correctly the following formula:

+sum({<DOT_STATUS={"Too late"}, ActualGoodsMovPeriod={">$(=max(YearMonth))"}, PlannedGoodsMovPeriod={"<$(=max(YearMonth))"}, NotDeliveredYet={0} >} YCOUNT)

it is important to mention that the calendar is not connected with the data model.

Re: Last 12 months

sunny_talwar — Mon, 03 Dec 2018 14:23:18 GMT

But why are you adding the part in red below?

date(date#(YWADAT,'YYYYMMDD'),'YYYYMM')&''    AS PlannedGoodsMovPeriod,

Re: Last 12 months

Zaga_69 — Mon, 03 Dec 2018 14:32:21 GMT

because I need "201801" as a text

not as a number like 42918

Re: Last 12 months

sunny_talwar — Mon, 03 Dec 2018 14:38:53 GMT

But if you store the value as text, then how will you do numeric comparisons (the ones you are looking to do).

I think there is some misunderstanding here. Date is a dual function which can store your value as 201801 and have a numeric value 42918. By not changing it into text, you can use it as a numeric field for set analysis comparison and still show it as 201801.

Try this

Date(Date#(YWADAT, 'YYYYMMDD'), 'YYYYMM') as PlannedGoodsMovPeriod, 
Date(Date#("0DATE", 'YYYYMMDD'), 'YYYYMM') as ActualGoodsMovPeriod,

and YearMonth like this

 Date(MonthStart(TempDate), 'YYYYMM') as YearMonth,

Now try this

Sum({<DOT_STATUS = {'Too Early'}, PlannedGoodsMovPeriod = {"$(='>=' & Date(AddMonths(Max(YearMonth), -12), 'YYYYMM) & '<=' & Date(Max(YearMonth), 'YYYYMM'))"}>} YCOUNT)

Re: Last 12 months

Zaga_69 — Mon, 03 Dec 2018 15:03:48 GMT

Hi,

you can determine if one string as number is smaller than another one

201810 is greater than 201809

201810 still can be interpreted as number

and you have 1 number for 1 month

with your solution proposed, we will have a PlannedGoodsMov and ActualGoodsMov as an specific day and not a month and I need just sth like 201701..

Re: Last 12 months

sunny_talwar — Mon, 03 Dec 2018 15:10:29 GMT

You are right, and to fix that... change the code to this

Date(MonthStart(Date#(YWADAT, 'YYYYMMDD')), 'YYYYMM') as PlannedGoodsMovPeriod, 
Date(MonthStart(Date#("0DATE", 'YYYYMMDD')), 'YYYYMM') as ActualGoodsMovPeriod,

Re: Last 12 months

Zaga_69 — Mon, 03 Dec 2018 15:48:22 GMT

Hi,

Why I should change the code if it works? 🙂

date(date#(YWADAT,'YYYYMMDD'),'YYYYMM')&'' AS PlannedGoodsMovPeriod,
date(date#("0DATE",'YYYYMMDD'),'YYYYMM')&'' AS ActualGoodsMovPeriod,

what I need now is to calculate in this formula the last 12 months

Sum({<DOT_STATUS={'Too Early'},PlannedGoodsMovPeriod={"$(=max(YearMonth))"}>}YCOUNT)

Thanks in advance,

Regards,

Edi

Re: Last 12 months

Zaga_69 — Mon, 03 Dec 2018 16:05:54 GMT

I ma trying to do sth like this, but it does not work:

Sum({<DOT_STATUS = {'Too Early'}, PlannedGoodsMovPeriod = {$(=date(addmonths(date(date#(max(YearMonth),'YYYYMM')),-12),'YYYYMM')&''}>} YCOUNT)

Re: Last 12 months

sunny_talwar — Mon, 03 Dec 2018 16:07:26 GMT

@Zaga_69 wrote:

Why I should change the code if it works? 🙂
date(date#(YWADAT,'YYYYMMDD'),'YYYYMM')&'' AS PlannedGoodsMovPeriod,
date(date#("0DATE",'YYYYMMDD'),'YYYYMM')&'' AS ActualGoodsMovPeriod,

because I am suggesting you to use the best practices... read here on some important links

QlikView Date fields

Get the Dates Right

Why don't my dates work?

Dates in Set Analysis

@Zaga_69 wrote:

what I need now is to calculate in this formula the last 12 months
Sum({<DOT_STATUS={'Too Early'},PlannedGoodsMovPeriod={"$(=max(YearMonth))"}>}YCOUNT)

Can't really help unless you stop doing a text to a text comparison, but may be someone else have better ideas 🙂

Re: Last 12 months

sunny_talwar — Mon, 03 Dec 2018 16:08:17 GMT

Make sense, but again.... I am not sure how you will do this with a text field 🙂

Re: Last 12 months

Zaga_69 — Tue, 04 Dec 2018 14:54:09 GMT

I ma trying to use the formula you have suggested, but in somehow it does not work...

Please see attached.

P.S: thank you for all documents you attached...it was quite helpful to understand better how Qlik works with dates

Re: Last 12 months

sunny_talwar — Tue, 04 Dec 2018 15:04:28 GMT

I am not sure I follow the issue just by looking at the image and some highlighting. Would you be able to put in words what is the issue?

Re: Last 12 months

Zaga_69 — Tue, 04 Dec 2018 15:11:16 GMT

The following formula brings some dates correctly, but in some dates it does not work:

date(MonthStart(Date#(YWADAT,'YYYYYMMDD')), 'YYYYMM') AS PlannedGoodsMovPeriod2,
date(MonthStart(Date#("0DATE",'YYYYYMMDD')),'YYYYMM') AS ActualGoodsMovPeriod2;

for example for 20140401 or 20181130 it brings "-".

what I am doing wrong?

Re: Last 12 months

sunny_talwar — Tue, 04 Dec 2018 15:29:26 GMT

I think you have an extra Y in your Date#() function... there should only be 4 YYYY

date(MonthStart(Date#(YWADAT,'YYYYYMMDD')), 'YYYYMM') AS PlannedGoodsMovPeriod2,
date(MonthStart(Date#("0DATE",'YYYYYMMDD')),'YYYYMM') AS ActualGoodsMovPeriod2;

Re: Last 12 months

Zaga_69 — Tue, 04 Dec 2018 16:02:40 GMT

You are right...it was a misspelling mistake 🙂

regarding to my initial question how to calculate the last twelve months I figured out that ii can be solved by the following formula:

Sum({<DOT_STATUS={'Too Early'},PlannedGoodsMovPeriod={"$(=max(YearMonth)-100)"}>}YCOUNT)

Since Qlik tries 201807 as string or number and if subtract -100 then I would have 201707...

Re: Last 12 months

sunny_talwar — Tue, 04 Dec 2018 16:06:46 GMT

That is great, but again, I wouldn't recommend using Dates like that... but if that is what you are comfortable with... then I wouldn't push you to much... but there are potential issues you might run into in the near future.

Best,
Sunny