Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have Fact table which has Actual sales data (Daywise)
I have a target data which is monthwise and yearwise, this target data is concatenated to the Fact Table
In the Actual Sales Data i have Date and Sales fields
In Target Data i have Year , Month and Value fields
What i want is
I have a KPI of Total Sales ie Sum(Sales) , this KPI works on selection of singel date and also with date rangeselection
I want to show Target
whenever the user selects a single date in Date field it should get the month and year from that selected date ,
and match that month and year in the target data and sum up the Value for that month year and show .
if the user selects date range for one full month and another half month then than target should show Value for two full months.
can anyone help me in writing the expression ?
Thanks in advance
If I understand you can try with this answer
Fact Table with Mixed Granularity
Another option could be in the attachment
Thanks for your reply
But i do not have access to script, and i have to handle in presentation layer.
is it possible using set analysis ??
This is my test script
SET DateFormat='DD/MM/YYYY';
Fact:
load *, 'Sales' as Type, Date(MonthStart(Date), 'YYYYMM') as MonthYear inline [
Date, Sales
01/01/2017, 100
02/01/2017, 100
03/01/2017, 100
01/02/2017, 200
02/02/2017, 200
03/02/2017, 200
01/03/2017, 300
02/03/2017, 300
03/03/2017, 300
];
Concatenate (Fact)
load *, 'Target' as Type, Date(MakeDate(Year, Month), 'YYYYMM') as MonthYear inline [
Month, Year, Value
1, 2017, 1000
2, 2017, 1100
3, 2017, 1200
];
and this the chart with expressions
sum({$ <Type={Sales}>} Sales)
sum({$ <Type={Target},Date=,MonthYear=P(MonthYear)>} Value)
Hi your code is working.
but
My Default Date format in app is
DateFormat='M/D/YYYY';
And in my Target data Month format is MMM
At my side the output is coming wrong
Thanks in Advance
I changed the format to M/D/YYYY
SET DateFormat='M/D/YYYY';
SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';
Fact:
load *, 'Sales' as Type, Date(MonthStart(Date), 'YYYYMM') as MonthYear inline [
Date, Sales
01/01/2017, 100
01/02/2017, 100
01/03/2017, 100
02/01/2017, 200
02/02/2017, 200
02/03/2017, 200
03/01/2017, 300
03/02/2017, 300
03/02/2017, 300
];
Concatenate (Fact)
load Month(MakeDate(Year, Month)) as Month, 'Target' as Type, Date(MakeDate(Year, Month), 'YYYYMM') as MonthYear, Value
inline [
Month, Year, Value
1, 2017, 1000
2, 2017, 1100
3, 2017, 1200
];
thanks
Try to close and mark help ful this thread because for next arrive issuers can recommend direct to that.