on 01-17-2017 4:11 PM
Hello All,
I have a need to show all months even though there are no transactions available for every month. My inputs are Start Month and End Month (201601 - 201612). But sicne not transactions available for these period I display only a few columns (201601, 201604, 201609, 201612). But I would like to generate 12 different values like 201601...201612 and display values only where there is a match for the rest of the dummy months I will display 0. Is there any way to do this web intelligence cross tabs?
Thank you,
Arthur.
Amit,
Yes it may give the result I am looking for, Since I did not have enough time. I have changed (not technically) and added the entry I wanted with another group and got the result I am looking for. I believe what you suggested should work.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Amit,
I already have the time dimension, which is merged with my model to generate the various dimensions of date/time. Yes I can create additional query and display only the month for the prompt and it will return 12 months. But if I merge with the transaction data-month (which I have only for 5 months) with Query2-Month (though it brings 12 months) after Merge will I not get just what is available in the transaction data? (I have tried it and this is what I get when the Merge dimension is used)... It is almost like an outer join result what I should use in my cross tab... (My transaction data is not there for some months, so for missing months I should have 0's or I should create them ins some other way..
Any other ideas?
Query1 Query 2 When Merge Dimension applied
201601 201601 201601
201604 201602 201604
201609 201603 201609
201612 201604 201612
201605
201606
201607
201608
201609
201610
201611
201612
Thank you,
Arthur.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Amit,
When we merge dimensions and check the extended box it should work, but due to my limited time, I added the entry I have issues with another group where some other rows will definitely have all 12 months. SO my issue is resolved in a different way without going thru too much more on this.
Merge Dimension must be the way unless an outer join is done at the universe. I don't know how to choose yrs as the final answer. But I am closing this.
Thank you,
Arthur.
If you have some date object then you can use TIMDIM & MonthPeriod function to generate continuous dates.
https://blogs.sap.com/2014/12/03/demystifying-the-timedim-function/
other option is to add one more webi query and select Month object only and add the same date range prompt condition in this query.
Merge both the Month object and use in the crosstab.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.