Re: how to calculate the fixed month and current m... - Qlik Community

Report Inappropriate Content · ‎2015-02-27

Hi all

I have table like this

name value date
it 10 31-jan-15

it 20 31-jan-15

it 30 31-jan-15

it 40 31-jan-15

it 10 28-feb-15

it 20 28-feb-15

it 30 28-jan-15

it 40 28-feb-15

i am calculating Pivot table like this

name basline value current value

it 10 10

it 20 20

it 30 30

it 40 40

baseline value means 'jan' data and current 'feb' data.

my qustion is how to calculate the basline and current value in set analysis..?

suppsoe i am adding march data to the table at that time baseline value 'jan' it is fix. but current value is 'march' data.

how to fix 'jan' data value in baseline column using set analysis..? and current value is change corresponding current month how to write expression in set anaysis..?

plz help me this part its urgent. plz write the discussion in discussion window only because i am using personal edition

plz help me any one

thanks in advance.

jyothish8807 · ‎2015-02-27

Hi,

Inscript:

Load

Month(Date) as Month

resident abc;

Try like this:

Dimension:

1.Name

Expression:

1.sum({<Month={'Jan'}>}Value)

2.sum({<Month={'$(=month(Max(Date))'}>}Value)

Regards

KC

Best Regards,
KC

ravindraa · ‎2015-02-27

Hi Subbu Jasti,

In edit script :

load

name,

value,

month(date) as Month

resident inline;

for baseline month find following expression: sum({<Month={"Jan"}>}value)

For current Month find the following expression sum({<Month={"$(=max(Month))"}>}value)

i hope it will work..

MK_QSL · ‎2015-02-27

Script

Table:

Load

*,

if(month = 'Jan',1,0) as FixedMonth;
Load

name,

value,

Date(date) as date,

Month(date) as month

From ......

Now Create a Straight Table

Dimension

name

Expression

SUM({<FixedMonth = {1}>}value)

SUM({<month = {'$(=Mon(max(date)))'}>}value)

kamalqlik · ‎2015-02-27

Hi Subbu,

As I understand your point...You want to say that Jan data will be fixed and Current Month Data will keep on changing.

For Example in Feb Baseline Data will be Jan

and Current Data will be Feb

In Case of Mar BaseLine Data will be Jan and Current Data will be Mar....

Use Like this

SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Depart:
LOAD * Inline
[
Department, Value, Date

it , 10 , 31/01/2015

it , 20 , 31/01/2015

it , 30 , 31/01/2015

it , 40, 31/01/2015

it , 10 , 28/02/2015

it , 20 , 28/02/2015

it , 30 , 28/02/2015

it , 30, 28/02/2015 ];

Depart1:
LOAD *,
Month(Date) as Month
Resident Depart;
DROP Table Depart;

//After this reload the Application.

In Front-end make an Variable vMAxDate=Month(Max(Date))

So Baseline Data will be----

=sum({<Month={'Jan'}>}Value)

and Current Data will be-

=sum({<Month={'$(MAxDate)'}>}Value) .

I think this will help you ...

Regards,

Kamal

Report Inappropriate Content · ‎2015-02-27

Thanks friend.its ok for 2015 data. Suppose i am taking in the date field values both years 2015 data and 2016 data.

2016 data like

name value date
it 10 01-jan-15

it 10 02-jan-15

it 10 05-jan-15

it 10 06-jan-15

it 10 31-jan-15

it 20 31-jan-15

it 30 31-jan-15

it 40 31-jan-15

it 10 02-feb-15

it 20 08-feb-15

it 30 28-feb-15

it 20 28-feb-15

it 30 28-jan-15

it 40 28-feb-15
it 10 31-jan-16

it 20 31-jan-16

it 30 31-jan-16

it 40 31-jan-16

it 10 28-feb-16

it 20 28-feb-16

it 30 28-jan-16

it 40 28-feb-16
The above situation how to achieve different years ?

how to achieve same month but different dates..?

the above two problems how to solve.?

plz tell me frnd. in dscussion window only.

thanks in advance.

Report Inappropriate Content · ‎2015-02-27

Thanks friend.its ok for 2015 data. Suppose i am taking in the date field values both years 2015 data and 2016 data.

2016 data like

name value date
it 10 01-jan-15

it 10 02-jan-15

it 10 05-jan-15

it 10 06-jan-15

it 10 31-jan-15

it 20 31-jan-15

it 30 31-jan-15

it 40 31-jan-15

it 10 02-feb-15

it 20 08-feb-15

it 30 28-feb-15

it 20 28-feb-15

it 30 28-jan-15

it 40 28-feb-15
it 10 31-jan-16

it 20 31-jan-16

it 30 31-jan-16

it 40 31-jan-16

it 10 28-feb-16

it 20 28-feb-16

it 30 28-jan-16

it 40 28-feb-16
The above situation how to achieve different years ?

how to achieve same month but different dates..?

the above two problems how to solve.?

plz tell me frnd. in dscussion window only.

thanks in advance.

Report Inappropriate Content · ‎2015-02-27

Thanks friend.its ok for 2015 data. Suppose i am taking in the date field values both years 2015 data and 2016 data.

2016 data like

name value date
it 10 01-jan-15

it 10 02-jan-15

it 10 05-jan-15

it 10 06-jan-15

it 10 31-jan-15

it 20 31-jan-15

it 30 31-jan-15

it 40 31-jan-15

it 10 02-feb-15

it 20 08-feb-15

it 30 28-feb-15

it 20 28-feb-15

it 30 28-jan-15

it 40 28-feb-15
it 10 31-jan-16

it 20 31-jan-16

it 30 31-jan-16

it 40 31-jan-16

it 10 28-feb-16

it 20 28-feb-16

it 30 28-jan-16

it 40 28-feb-16
The above situation how to achieve different years ?

how to achieve same month but different dates..?

the above two problems how to solve.?

plz tell me frnd. in dscussion window only.

thanks in advance.

Report Inappropriate Content · ‎2015-02-27

Thanks friend.its ok for 2015 data. Suppose i am taking in the date field values both years 2015 data and 2016 data.

2016 data like

name value date
it 10 01-jan-15

it 10 02-jan-15

it 10 05-jan-15

it 10 06-jan-15

it 10 31-jan-15

it 20 31-jan-15

it 30 31-jan-15

it 40 31-jan-15

it 10 02-feb-15

it 20 08-feb-15

it 30 28-feb-15

it 20 28-feb-15

it 30 28-jan-15

it 40 28-feb-15
it 10 31-jan-16

it 20 31-jan-16

it 30 31-jan-16

it 40 31-jan-16

it 10 28-feb-16

it 20 28-feb-16

it 30 28-jan-16

it 40 28-feb-16
The above situation how to achieve different years ?

how to achieve same month but different dates..?

the above two problems how to solve.?

plz tell me frnd. in dscussion window only.

thanks in advance.

how to calculate the fixed month and current month varing..?