Last 12 months

[Zaga_69]

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

[Zaga_69]

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

[Zaga_69]

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)

[sunny_talwar]

---

@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 🙂

[sunny_talwar]

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

[Zaga_69]

Hi

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

[sunny_talwar]

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?

[Zaga_69]

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?

[sunny_talwar]

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;

[Zaga_69]

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...

[sunny_talwar]

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