Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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....
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
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....
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
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.
And if the solution is correct and solves your problem, then please mark it as correct.
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.
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
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.
Dear Rajat
Thanks for you explanation
I have attache the file. Pls send me the modified files with requirements requested by me earlier