On selection of data or date range show full month and Year value?

[gauravgg]

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

[maxgro]

If I understand you can try with this answer

Fact Table with Mixed Granularity

Another option could be in the attachment

[gauravgg]

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

[maxgro]

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)

[gauravgg]

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

[maxgro]

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

];

[gauravgg]

thanks

[Anil_Babu_Samineni]

Try to close and mark help ful this thread because for next arrive issuers can recommend direct to that.