topic Re: Year Month in QlikView

Year Month

upaliwije — Sat, 12 Apr 2014 16:00:16 GMT

Hi Friends

I have attached a QVW file and the supporting work sheet. I want your help to do the following

1.Create master Calender with Year_Month as field

2. When I select an Year_Month that particular month should be the 1st Column of the Pivot table other months after that

Eg : If I select 2013_05 as Year_Month my pivot table should look like as follows

YEAR	MONTH	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR
2013	1	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,139,520	6,129,330	4,060,620
2013	2	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	6,129,330	4,060,620
2013	3	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	4,060,620
2013	4	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	5	5,017,381	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	6	5,017,381	3,978,719	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	7	5,017,381	3,978,719	4,813,178	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	8	5,017,381	3,978,719	4,813,178	4,772,933	4,492,297	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	9	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	5,708,382	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	10	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	5,472,065	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	11	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	4,843,327	5,573,470	4,122,270	5,225,041	5,436,199	3,911,853
2013	12	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	4,843,327	3,715,368	4,122,270	5,225,041	5,436,199	3,911,853
2013	Total	61,470,245	51,743,404	######	######	59,315,184	######	64,407,304	######	49,467,242	62,614,973	66,620,652	47,388,537

Pls help me to solve the issue. If You can write the script in my document so much the better

Thanks in advance

Re: Year Month

MarcoWedel — Sun, 13 Apr 2014 01:16:33 GMT

Hi upali,

one solution could be:

tabProduction:
CrossTable (MON, MonthValue, 3)
LOAD
  Date(MakeDate(YEAR, MONTH), 'MM/YYYY') as Year_Month,
  *
FROM [http://community.qlik.com/servlet/JiveServlet/download/507615-101734/PRDUCTION.xls]
(biff, embedded labels, table is Sheet1$);

Left Join
LOAD
  MON,
  Date(Date#(MON&'/'&min(YEAR), 'MMM/YYYY'), 'MMM') as MonthName
Resident tabProduction
Group By MON;

tabMasterCalendar:
LOAD Distinct
  Year_Month,
  YEAR,
  MONTH
Resident tabProduction;

DROP Field MON;
DROP Fields YEAR, MONTH From tabProduction;

I CrossTable loaded your table and converted the newly created MonthName field into a date i.e. it can be sorted numericaly.

To achieve the correct sorting by selected Year_Month, I used a sorting expression on the MonthName dimension of the pivot table:

=if(GetSelectedCount(Year_Month)=1,
    if(Month(MonthName)<MONTH, AddMonths(MonthName, 12), MonthName)
   )

hope this helps

regards

Marco

Re: Year Month

rajat2392 — Sun, 13 Apr 2014 06:17:15 GMT

Here is the solution, but it will work for this data only, having a single year....

if you have other years also, then you have to do a little alteration in this....

Re: Year Month

upaliwije — Sun, 13 Apr 2014 06:40:38 GMT

Thanks Rajat

It is fine and working well. As you said there are more than one year. Pls let me know what alteration needs to be done to the data model

Re: Year Month

rajat2392 — Sun, 13 Apr 2014 07:00:36 GMT

If the data will be maintained as it is as the current schema, then this solution will work for that also, because the dimension year and month will automatically take the associated data. I schema changes then you have to set set-modifier. As for now, for same data, no change is needed.

Re: Year Month

rajat2392 — Sun, 13 Apr 2014 07:01:34 GMT

And if the solution is correct and solves your problem, then please mark it as correct.

Re: Year Month

rajat2392 — Sun, 13 Apr 2014 07:11:57 GMT

sorry but not this one, that one in which I have attached the files, so that anyone else having same problem can find the solution.

Re: Year Month

upaliwije — Sun, 13 Apr 2014 07:18:16 GMT

Thanks a Lot

Can you please explain me how the following Expression work

sum({1}$(=Upper(Month(AddMonths(MakeDate(SubField(Year_Month,'_',1), SubField(Year_Month,'_',2)),0)))))

Say If I want to rename my field in Production work sheet where can I replace new field in above expression

Eg Instead of Jan can I say NRP

Re: Year Month

rajat2392 — Sun, 13 Apr 2014 07:46:56 GMT

Ya sure, see...

while loading the data I have created a field called "Year_Month" as YEAR & '_' & MONTH as Year_Month

this same field is used as filter above on the dashboard...

now as your data has Months name (JAN, FEB, MAR...) as fields, so getting that name based on the selection, "SubField(Year_Month,'_',2)" this expression will extract the month number there and "SubField(Year_Month,'_',1)" this will get the year..for ex- if 2013_6 is selected, then in it will return 6 and 2013 respectively in makedate function. Makedate function will return 1/6/2013. Now addmonths add the no. of months, so in this expression 0 is there, so date remain as it is, for next expresiion, 1 is added and gradually increased till 11. and then Month function will return month name as "Jun". But in your data column name is in uppercase, so I have added upper function also to make it "JUN"

Sum({1}...)..this is to nullify all the selection on dashboard, if you won't add this, it will return only one row, that is based on selection.

Now in data if u change Jan to NRP, it will not work, because date functions I have used here, so it is returning JAN.

If you want to change then send me a sample data and I will tell you the solution then, as of now without getting perfect understanding of data arrangement, I can't give exact answer.

Re: Re: Year Month

upaliwije — Sun, 13 Apr 2014 08:08:16 GMT

Dear Rajat

Thanks for you explanation

I have attache the file. Pls send me the modified files with requirements requested by me earlier

Re: Re: Year Month

rajat2392 — Sun, 13 Apr 2014 12:51:36 GMT

here is the solution for this problem..see the attached files....

Pls don't forget to mark it as correct if u are satisfied with the solution....

Re: Year Month

MarcoWedel — Sun, 13 Apr 2014 15:22:55 GMT

@QlikCommunity moderator:

why is my first answer to this thread still in moderation status and thus invisible to the OP?

Re: Year Month

MarcoWedel — Sun, 13 Apr 2014 22:28:29 GMT

@QlikCommunity moderator:

I still would appreciate an explanation as to why my post triggered the moderation function ...

Re: Year Month

upaliwije — Wed, 16 Apr 2014 11:30:37 GMT

Hi Rajat

Thanks a lot for your help

sum({1}$(=Upper(Month(AddMonths(MakeDate(SubField(Year_Month,'_',1), SubField(Year_Month,'_',2)),0)))))

Now I want to modify the above expression to show data relating to selection made. Eg If I select Year as 2013 only the following records should be shown. Pls help me

YEAR	MONTH	JAN_NRP	FEB_NRP	MAR_NRP	APR_NRP	MAY_NRP	JUN_NRP	JUL_NRP	AUG_NRP	SEP_NRP	OCT_NRP	NOV_NRP	DEC_NRP
2013	1	4122270.14	5139519.86	6129330.21	4060620.24	5332798.8	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	2	4122270.14	5225041.15	6129330.21	4060620.24	5332798.8	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	3	4122270.14	5225041.15	5436199.16	4060620.24	5332798.8	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	4	4122270.14	5225041.15	5436199.16	3911852.94	5332798.8	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	5	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	4778474.83	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	6	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	5820450.55	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	7	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4327208.28	4492296.77	5708382.43	5472064.98	5573470.37
2013	8	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	4492296.77	5708382.43	5472064.98	5573470.37
2013	9	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	5844202.47	5708382.43	5472064.98	5573470.37
2013	10	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	5844202.47	4616778.98	5472064.98	5573470.37
2013	11	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	5844202.47	4616778.98	4843327.16	5573470.37
2013	12	4122270.14	5225041.15	5436199.16	3911852.94	5017381.25	3978718.51	4813178.49	4772932.54	5844202.47	4616778.98	4843327.16	3715367.73

Re: Re: Year Month

rajat2392 — Wed, 16 Apr 2014 12:19:17 GMT

Hi Upali,

check the attached app....

Re: Year Month

upaliwije — Wed, 16 Apr 2014 15:59:37 GMT

Dear Rajat

Thanks a lot . It is working Fine

Re: Year Month

rajat2392 — Thu, 17 Apr 2014 03:53:50 GMT

welcome Upali..can you please mark it as correct...

Re: Year Month

upaliwije — Thu, 17 Apr 2014 07:28:32 GMT

Dear Rajat

Thanks a lot for your help.

I have a small issue and need your help to solve.

I want to create a pivot table with a single row which should show the data highlighted in bold letteers by me in a single Row. All other conditions applied to previous pivot table should remain unchanged.

YEAR	MONTH	JAN_NRP	FEB_NRP	MAR_NRP	APR_NRP	MAY_NRP	JUN_NRP	JUL_NRP	AUG_NRP	SEP_NRP	OCT_NRP	NOV_NRP	DEC_NRP
2013	1	4,122,270	5,139,520	6,129,330	4,060,620	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	2	4,122,270	5,225,041	6,129,330	4,060,620	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	3	4,122,270	5,225,041	5,436,199	4,060,620	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	4	4,122,270	5,225,041	5,436,199	3,911,853	5,332,799	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	5	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	4,778,475	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	6	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	5,820,451	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	7	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,327,208	4,492,297	5,708,382	5,472,065	5,573,470
2013	8	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	4,492,297	5,708,382	5,472,065	5,573,470
2013	9	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	5,708,382	5,472,065	5,573,470
2013	10	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	5,472,065	5,573,470
2013	11	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	4,843,327	5,573,470
2013	12	4,122,270	5,225,041	5,436,199	3,911,853	5,017,381	3,978,719	4,813,178	4,772,933	5,844,202	4,616,779	4,843,327	3,715,368