Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have set of Fields;
Year = 2016,2017
HalfYear = FY17-H1, FY17-H2
Quarter = FY17-Q1, FY17-Q2, FY17-Q3, FY17-Q4
And trying to create a straight table using Set Analysis:
Sum({<Region = {'North'}, Year= 2017>}Sales)
Sum({<Region = {'North'}, HalfYear= {'FY17-H1'}>}Sales)
Sum({<Region = {'North'}, Quarter={'FY17-Q4'}>}Sales)
Is it possible to write ONE expression and the values to be changed based on the above selections (Year, HalfYear, Quarter);
Hope we can use Getfieldselections() or Getcurrentselections() etc.
Also, I need to write expressions by comparing two dates:
Ex: (Sum({<Region = {'North'}, Quarter={'FY17-Q4'}>}Sales - Sum({<Region = {'North'}, Quarter={'FY16-Q4'}>}Sales)*100
How to automate both expressions, so that the values should change in the same object based on ONE Selection.
Reg,
Mrutyunjaya
Hi Mrutyunjaya,
If you use the field selections, you wouldn't have to use Set Analysis (except for the region).
Hi Mrutyunjaya,
I think you can solve your first request just using expression Sum({<Region = {'North'}>}Sales) and then changing selection for Year, HalfYear and Quarter directly in layout.
However, your second request is a YTD analysis and you can build it with the use of variables, for es. vMaxQuarter and vMaxQuarter_m1, which can be populated dinamically with your selections, and replace 'FY17-Q4' and 'FY16-Q4' in set analysis.
I'm used to define a quarter as a number: 20171 (Q1), 20172 (Q2)... That's useful in variables and I can define vMaxQuarter as
year((v_max_date))*10+((month((v_max_date))-if(mod(month((v_max_date)),3)=0,3,mod(month((v_max_date)),3)))/3+1)
and then vMaxQuarter_m1 as vMaxQuarter-10.
Here I suppose I have a date field to select so that v_max_date can be max(datefield) or only(datefield), which changes according with selections. If you have not, you have to determine the 2 variables above through the analysis of the text 'FY17-Q4' selected ...
I hope it helps.
Bye
IB
Hi Irene,
Thanks...that is really helpful.
The first part..YES you are right & it works the way you said (I added it for the ease of understanding)
I am actually looking for the second part - where I have created 50+objects for Quarterly views using;
Ex: (Sum({<Region = {'North'}, Quarter={'FY17-Q4'}>}Sales - Sum({<Region = {'North'}, Quarter={'FY16-Q4'}>}Sales)*100,
And added $(vCurrentQuarter) & $(vPrevQuarter) wherein I always get the values of 'CurrentQuarter' and 'PrevQuarter' based on today().
However, I am trying to make the same objects dynamic based on Selections (Year, HalfYear, Quarter) Else I will end up creating
50 more objects for Year
50 more objects for Halfyear
Please suggest how can I automate it better.
Reg,
Mrutyunjaya
Hi Mrutyunjaya,
I attach a qvf file example with sample data to show you exactly what I mean and what I think could be done.
In the qvf you will find just one sheet: up a list box with Year, YearMonth and Quarter available for direct selection; down 2 tables. The first table has Total Sales column, Today Q. Sales, Today Q. Sales (Y-1), Max Q. Sales and Max Quarter Sales (Y-1).
The last two columns dinamically change with selections.
Then, if you'd like just 2 columns and not 4, you could use getselectioncount as you suggested: you will find my example in the second table. Varying selections on Year, YearMonth or Quarter the Sales values will adapt.
However set analysis is always based on Quarter field: if you want YTD Analysis even on Month or Year base, you have to create different expressions...
One hint, in this situation (but I don't know if this scenario is suitable for you), I prefer reload data till today and so make today coincide with max date, in order to always use "Max" conditions, avoiding the use of getselectioncount or getfieldselection which could cause problems with selections not prevented...
Hope it helps.
IB