topic Re: How to get only few month data based on monthnumbers. in QlikView

How to get only few month data based on monthnumbers.

Srinivas — Wed, 03 Sep 2014 11:59:26 GMT

Dear all,

Please see below My data,

Here i need for 2013 year hole sales and for 2014 year Jan to May data i need in my chart.

i wrote expression like this way but it's not working properly. =sum({$<Year = {$(=max(Year))}, Month =>} AOP_Cts/1000)

2013= here need Jan to Dec ...hole data

2014 = here i need Only Jan to May only

how we can write expression....

Year	Month	Sales
2013	1	20.946
2013	2	21.061
2013	3	21.92
2013	4	21.848
2013	5	16.645
2013	6	23.912
2013	7	25.137
2013	8	24.963
2013	9	25.113
2013	10	23.429
2013	11	5.267
2013	12	21.411
2014	1	16.141
2014	2	15.855
2014	3	19.934
2014	4	21.189
2014	5	13.436
2014	6	20.667
2014	7	24.445
2014	8	24.596
2014	9	25.537
2014	10	22.119
2014	11	1.271
2014	12	15.13

Advance Thanks

Munna

Re: How to get only few month data based on monthnumbers.

alexandros17 — Wed, 03 Sep 2014 12:06:14 GMT

The best thing is to create a new field in your script that concatenate year and month:

Load

...

year * 100 + month as yearmonth

...

resident

...

In this way you will have a field like 201307 ... 201401, 201402 ...

the expression become:

sum({$<yearmonth = {'<=201405'}>} AOP_Cts/1000)

obviously you can use a variable for the current month ...

Let me know

Re: How to get only few month data based on monthnumbers.

sundarakumar — Wed, 03 Sep 2014 12:06:26 GMT

i would suggest to create yeamonth field in the back end and use it in set analysis.

Here u have condition like u should show all data before 2014 may,

can be done like

sum({<Yearmonth={'<201405'}>}sales)

u wil need to do some changes in back end like

num(Year&if(len(Month)=1,'0'&Month,Month)) as Yearmonth

U can even move 201405 to a vaiable for further modifications ion it.

-Sundar

Re: How to get only few month data based on monthnumbers.

Srinivas — Thu, 04 Sep 2014 02:00:02 GMT

Hi Sundarakumar,

As per ur expression we are getting only one (May)month data but i want in year 2014 jan+feb+mar+Apr+May... this five month data in this year, And Previous year 2013 i want hole data like Jan+feb+Mar....upto Dec..

Advance Thanks

Munna

Re: How to get only few month data based on monthnumbers.

jagan — Thu, 04 Sep 2014 02:13:49 GMT

Hi Munna,

Try like this

Script:

TableName:

LOAD

Num(Year & Num(Month, '00')) AS YearMonth

FROM DataSource;

Now use this expression

sum({<Year=, Month=, Yearmonth={'<201405'}>} Sales)

Hope this helps you.

Regards,

Jagan.

Re: How to get only few month data based on monthnumbers.

sundarakumar — Thu, 04 Sep 2014 04:04:15 GMT

sum({<Yearmonth={ < 201405}>}sales)

please note the less than symbol in set analysis it will look for yearmonths which is less than 201405 which means all the years and month before it. if u want a from date also u can very well add it as

sum({<Yearmonth={ < 201405>201301}>}sales)

we would suggest to move these numbers to variables to make it dynamic.

-Sundar

Re: How to get only few month data based on monthnumbers.

Anonymous — Thu, 04 Sep 2014 05:22:14 GMT

Hi Munna,

After creating Yearmonth column, you may try this :

sum({<Yearmonth={<=201405}>}sales)

Regards,

Chinna

Re: How to get only few month data based on monthnumbers.

Srinivas — Thu, 04 Sep 2014 09:14:11 GMT

Hi Jagan,

It's working fine but statically it shows correct when i select any one there's no any changes ..it is not a Dynamically .

Could you please check once more.

Regards

Munna