topic Re: AddMonths problem in Set Analysis in QlikView

AddMonths problem in Set Analysis

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

Hi,

i´m experiencing some problems related to the use of this function with set analysis.

My requeriment is to calculate the sum of sales in a three month period of time based in the Start Date of a Campaign.

I have this data:

id	SalesDate	Sales
1	20180101	0
1	20180201	10.000
1	20180301	40.000
1	20180401	50.000

So, the result for Id=1 should be 50.000 when selecting 20180101

But i can´t get this value. My attempts are:

- sum({$<SalesDate={">=$(=20180101)<=$(=20180301)"}>}Sales)

This goes ok. Result=50.000

- sum({$<SalesDate={">=$(=Date(Start_Date))<=$(=20180301)"}>}Sales)---->Start_Date=20180101 in this case.

This goes ok. Result=50.000

- sum({$<SalesDate={">=$(Date(Start_Date))<=$(AddMonths(Date(Start_Date),2))"}>}Sales)

--->AddMonths(Date(Start_Date),2)=20180301 in this case.

This goes wrong. Result=100.000

It seems AddMonths function is not working. Any idea would be appreciated.

Thanks in advance.

Regards.

Re: AddMonths problem in Set Analysis

tamilarasu — Mon, 05 Feb 2018 07:44:30 GMT

Hi Nacho,

sum({$<SalesDate={">=$(=Date(Start_Date))<=$(=AddMonths(Date(Start_Date),2))"}>}Sales)

Re: AddMonths problem in Set Analysis

nachofernandez — Mon, 05 Feb 2018 08:03:37 GMT

Thank you

but I´m afraid it doesn´t work either.

The result with that expression is 0.

But if i change AddMonths(Date(Start_Date),2) with 20180301 it goes perfect. Something´s wrong with AddMonths.

Re: AddMonths problem in Set Analysis

tamilarasu — Mon, 05 Feb 2018 08:10:48 GMT

Got it.

sum({$<SalesDate={">=$(=Date(Start_Date))<=$(=Date(AddMonths(Date#(SalesDate,'YYYYMMDD'),2),'YYYYMMDD') )"}>}Sales)

If your SalesDate field is already formatted as date format means you can simply try,

sum({$<SalesDate={">=$(=Date(Start_Date))<=$(=Date(AddMonths(SalesDate,2),'YYYYMMDD') )"}>}Sales)

Re: AddMonths problem in Set Analysis

nachofernandez — Mon, 05 Feb 2018 08:46:17 GMT

No, sorry,

None of them work.

Re: AddMonths problem in Set Analysis

ruanhaese — Mon, 05 Feb 2018 09:00:21 GMT

There are other approaches you can take if your struggling with this.

(1) Add a field in the load script that highlights it as part of that 3 month group.

(2) Another simpler approach might be to try the RangeAvg function

RangeAvg - script and chart function ‒ QlikView

You can use this directly in your expressions

Re: AddMonths problem in Set Analysis

tamilarasu — Mon, 05 Feb 2018 09:17:18 GMT

I am not sure but it's working for me.

Try removing the label and see how expression is evaluated. If you still have any issues, please upload a small sample file.

Re: AddMonths problem in Set Analysis

jasonmomoa — Mon, 05 Feb 2018 09:56:04 GMT

Hello nachofernandez‌,

Whenever I try to do set analysis with dates, I always convert them to numerical values first. As we are performing logical operations (>= <=), it is best to work with integers rather than date formats that may cause you some nightmares.

Find below my notes.

Script:

LOAD * INLINE [
 id, SalesDate,  SalesDateNum, Sales
 1,  01/01/2018, 43101,        0
 1,  01/01/2018, 43132,        10000
 1,  01/01/2018, 43160,        40000
 1,  01/01/2018, 43191,        50000
];


Let Start_Date = Num(Date(MakeDate(2018, 1, 1)))
Let End_Date  = Num(AddMonths($(Start_Date), 2))

Set Analysis (see the flow):

SUM( {<[SalesDateNum]={">= 43101 <= 43160"}>} [Sales]) --> 50k

SUM( {<[SalesDateNum]={">= 43101 <= $(End_Date)"}>} [Sales])--> 50k

SUM( {<[SalesDateNum]={">= 43101 <= $(=Num(AddMonths($(Start_Date), 2)))"}>} [Sales]) --> 50k (Notce $=(..) )

SUM( {<[SalesDateNum]={">= 43101 <= Num(AddMonths($(Start_Date), 2)))"}>} [Sales]) --> 100k

Most probable, your error was due to the $(= ..) variable inside your SalesDate expression:

Not working: Sum({$<SalesDate={">=$(Date(Start_Date))<= $(AddMonths(Date(Start_Date),2))"}>}Sales)

Working: Sum({<[SalesDateNum]={">= 43101 <= $(=Num(AddMonths($(Start_Date), 2)))"}>} [Sales])

You could replicate these numbers using your SalesDate in date format. But be aware that even though you type in 20180101, this may not be a numerical value but a date value. To prevent such discrepancies, I highly recommend you to use numerical values always.

Hope it helps 🙂

Saludos,

Re: AddMonths problem in Set Analysis

nachofernandez — Tue, 06 Feb 2018 08:09:43 GMT

Thank you.

I´ve tried with num fields, but i get the same answer.

I attach a test document where you can see the problem.

I want the sum of sales for 3 months based on the Start Date Campaign.

For ID 1 the correct answer is 50.000. I only get this number if i use the exact date on the Set Analysis Formula.

Re: AddMonths problem in Set Analysis

jasonmomoa — Tue, 06 Feb 2018 09:21:00 GMT

In your test here, the Date_ID from your Sales inline table is not related to the Date_ID_num from your Calendar table.

You can create a simple table box with fields Date_ID, Date_ID_Num, ID and Sales. You'll see there is no relationship. Therefore, your last expression test using Num() dates in the set analysis won't work.

Try creating the very first input table with Dates(), rather than just typing the id in YYYYMMDD format.

Regards,

Re: AddMonths problem in Set Analysis

nachofernandez — Tue, 06 Feb 2018 18:22:22 GMT

Almost got it.

I´ve changed the scritp and now they´re connected.

If i select ID 1 it works. If i don´t select anything, the result is 0.

I need the table complete, without selecting anything.

Re: AddMonths problem in Set Analysis

nachofernandez — Wed, 07 Feb 2018 07:08:54 GMT

I think I´ve found the solution.

It seems Set Analysis is not applicable to this situation. Just if a record or a date is selected, but with no selection it doesn´t work. Changing it for an if function, it works very well.

Thank you very much for all the answers. They helped me a lot.