topic Re: Last month's day value issue in QlikView

Last month's day value issue

Tue, 04 Aug 2015 13:05:23 GMT

Hello everyone!

I have this kind of table:

How can I have a third column with values of the last day of each month? Something like this:

Thanks in advance!

Re: Last month's day value issue

jonathandienst — Tue, 04 Aug 2015 13:08:16 GMT

If you have a Month field and you add that as a dimension, it is simply:

Sum(TOTAL <Month> Value)

Re: Last month's day value issue

dickelsa — Tue, 04 Aug 2015 13:10:31 GMT

What you can do is:

Temp_1

Load

Date,

Month(Date) as Month,

Day(Month) as Day,

Value

Resident <Table> Order by Date DESC;

next you can use Peek function to see if the month's differentiate and if the day is different, and create a new column

Best regards,

Dick

Re: Last month's day value issue

Tue, 04 Aug 2015 13:17:59 GMT

I need this in the load script, because I'll create an apply map with this table, related to this third column

Re: Last month's day value issue

Tue, 04 Aug 2015 13:25:36 GMT

Actually, what I need is a new dimension (created in load script), not a simple column by set analysis.

Re: Last month's day value issue

sorrakis01 — Tue, 04 Aug 2015 13:55:26 GMT

Hi,

Try this:

Datos:

LOAD * INLINE [

Date, Value

01/07/2015, 423

10/07/2015, 567

15/07/2015, 678

20/07/2015, 45

31/07/2015, 678

01/08/2015, 4576

10/08/2015, 657

14/08/2015, 432

21/08/2015, 6789

27/08/2015, 213

31/08/2015, 3467

];

Mapping:

MAPPING LOAD

Month(Date) as Month,

Sum(Value) as ValueAggr

Resident Datos

group by Month(Date);

Datos2:

LOAD Date,

Month(Date) as Month,

ApplyMap('Mapping',Month(Date)) as ValueAggr,

Value

Resident Datos;

Drop table Datos;

Regards,

Re: Last month's day value issue

jonathandienst — Tue, 04 Aug 2015 13:57:51 GMT

Marcelo Hernandes wrote:

I need this in the load script, because I'll create an apply map with this table, related to this third column

Are you sure that is a good idea? It will limit the ability of the users to slice and dice the data. It is simple enough to calculate in the front end.

Re: Last month's day value issue

maximiliano_vel — Tue, 04 Aug 2015 13:58:02 GMT

Values:

LOAD Date,

Value,

Month(Date) as Month

;

LOAD * INLINE [

Date, Value

07/01/2015, 423

07/10/2015, 567

07/15/2015, 678

07/20/2015, 45

07/31/2015, 678

08/01/2015, 4576

08/10/2015, 657

08/14/2015, 4132

08/21/2015, 6789

08/27/2015, 213

08/31/2015, 3467

];

JOIN

LOAD Month as Month,

Max(Date) as Date,

1 as IsMaxDate

RESIDENT Values

GROUP BY Month;

LEFT JOIN

LOAD Month,

Value as LasDayValue

RESIDENT Values

WHERE IsMaxDate =1;

DROP Field IsMaxDate From Values

Re: Last month's day value issue

Kushal_Chawda — Tue, 04 Aug 2015 14:14:17 GMT

try this

Data:

LOAD date(MakeDate(2014)+IterNo()-1,'DD-MM-YYYY') as Date,

monthname(MakeDate(2014)+IterNo()-1) as MonthName,

Ceil(Rand()*100+1000) as Value

AutoGenerate(1)

While IterNo()-1 <= Today()-MakeDate(2014);

Left Join

LOAD Distinct MonthName ,

max(Date) as Date,

1 as MaxDateFlag

Resident Data

Group by MonthName;

Left Join

LOAD Distinct MonthName,

sum(Value) as MaxValue

Resident Data

where MaxDateFlag=1

Group by MonthName;

Re: Last month's day value issue

Tue, 04 Aug 2015 14:19:12 GMT

Thanks, but the ValueAggr gives the month's sum values, not the last day value of each month:

What I expect is to see is the value 678 in all July days. And 3467 in all August days.

Ps: here, the data format is DD/MM/YYYY

Re: Last month's day value issue

sorrakis01 — Tue, 04 Aug 2015 14:22:24 GMT

Hi,

Do you want the last value of each month? i think you want the month's values.

The values are fictitious of INLINE:

Regards

Re: Last month's day value issue

Tue, 04 Aug 2015 14:30:02 GMT

That's it! It worked here.

Thanks a lot, Maximiliano!!!

Re: Last month's day value issue

Tue, 04 Aug 2015 14:31:31 GMT

Yes, the last value of each month.

It has been solved yet.

Thanks anyway.