Year Month

[upaliwije]

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

[MarcoWedel]

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

[rajat2392 · Accepted solution]

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....

[upaliwije]

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

[rajat2392]

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.

[rajat2392]

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

[rajat2392]

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.

[upaliwije]

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

[rajat2392]

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.

[upaliwije]

Dear Rajat

Thanks for you explanation

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