topic Re: scenario in QlikView

scenario

Wed, 25 Nov 2020 16:16:04 GMT

Hi friends,

Product_id	Date	amount
A	1/5/2013	100
A	1/6/2014	200
A	1/7/2015	300
B	1/7/2013	200
B	1/8/2014	300
B	1/9/2015	400

this is my table here my requirement how to get last 2 years total(100+200)=300

as well how to get last 3 years amount 600

how to achieve this , please help me on this

Re: scenario

MK_QSL — Tue, 29 Nov 2016 10:13:24 GMT

Do you want this at back end?

If front end, where you are going to use this?

In chart or in text box?

Re: scenario

Anil_Babu_Samineni — Tue, 29 Nov 2016 10:31:15 GMT

What was the expected output? Does this happen with Product wise or what? Would you describe more

Re: scenario

Tue, 29 Nov 2016 12:01:35 GMT

Hi Manish,

his requirment is

Product 2 years 3 years

A (100+200)= 300 100+200+300

B same like A .

in front end only.

Re: scenario

Tue, 29 Nov 2016 12:01:43 GMT

his requirment is

Product 2 years 3 years

A (100+200)= 300 100+200+300

B same like A .

in front end only.

Re: scenario

Tue, 29 Nov 2016 12:03:04 GMT

Could you suggest me best material for Set analysis. and performance improve steps.

Re: scenario

Tue, 29 Nov 2016 12:03:10 GMT

Could you suggest me best material for Set analysis. and performance improve steps.

Re: scenario

Anil_Babu_Samineni — Tue, 29 Nov 2016 14:11:44 GMT

His means (User)

Can you try something like this

241345:

LOAD * Inline [

Product_id, Date1, amount

A, 1/5/2013, 100

A, 1/6/2014, 200

A, 1/7/2015, 300

B, 1/7/2013, 200

B, 1/8/2014, 300

B, 1/9/2015, 400

];

Final_Table:

NoConcatenate

LOAD *,Right(Date1,4) as Year

Resident 241345;

DROP Table 241345;

Take Straight Table

Dimension is -- Product_id

For 2 Years -- Sum({<Year = {"$(='<' & Max(Year))"}>} amount)

For 3 Years -- Sum(amount)

Seems O/P this, For Ref, Enclose is attached

Re: scenario

simran04 — Tue, 20 Mar 2018 08:35:11 GMT

Please try the below code:

INPUT:

LOAD * INLINE [

Product_id,Date,amount

A,1/5/2013,100

A,1/6/2014,200

A,1/7/2015,300

B,1/7/2013,200

B,1/8/2014,300

B,1/9/2015,400

];

INPUT1:

LOAD Product_id,

SUM(If((Year(Date#(Date,'DD/MM/YYYY'))='2013' OR Year(Date#(Date,'DD/MM/YYYY'))='2014'),amount,0)) as twoyearssum,

Sum(amount) as Threeyearssum

resident INPUT

group by Product_id;