Problem to get month from multiple fields

[#1]

Hi All,

i have 4 fields Planned Start, Actual Start, Planned Finish,Actual Finish.

These 4 fields contains different values(you can observe in second row), My requirement is need to display in bar chart like

if planned start is '1/10/2014', actual start is '2/21/2014', Planned Finnish is'1/24/2014', actual finish is '3/3/2014'

then i want to display count(planned start) and count(planned Finish) under jan month in 2 dif bars,count(actual start), count(actual finish) under feb month in 2 diff bars.

Like that i have to do for all the records.

i'm attaching the sample file also, could you please anybody take a look into this and give me some suggestion or sample QVW.

Thanks,

MV

[#2]

Hi All,

Any ideas plzzzzzz, it's an urgent req

Thanks,

MV

[#3]

Hi,

try using an InLine Calendar to select Year and Month (in number), then get Year and Month (in number) from all your dates fields, finally use set analysis to get your differents columns:

Ex of script:

Calendar:
LOAD * INLINE [
    Year, Month
    2013, 1
    2013, 2
    2013, 3
    2013, 4
    2013, 5
    2013, 6
    2013, 7
    2013, 8
    2013, 9
    2013, 10
    2013, 11
    2013, 12
    2014, 1
    2014, 2
    2014, 3
    2014, 4
    2014, 5
    2014, 6
    2014, 7
    2014, 8
    2014, 9
    2014, 10
    2014, 11
    2014, 12
];  // Complete or generate all your periods

Data:
LOAD [Planned Start],
  Year([Planned Start]) as Year_PS,
  Num(Month([Planned Start])) as Month_PS,
     [Actual Start],
     Year([Actual Start]) as Year_AS,
     Num(Month([Actual Start])) as Month_AS,
     [Planned Finish],
     Year([Planned Finish]) as Year_PF,
     Num(Month([Planned Finish])) as Month_PF,
     [Actual Finish],
     Year([Actual Finish]) as Year_AF,
     Num(Month([Actual Finish])) as Month_AF    
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);

In expression of your bar chart:

Count({<Year_PS={$(#=Only(Year))},Month_PS={$(#=Only(Month))}>}[Planned Start]) // First bar

Count({<Year_PF={$(#=Only(Year))},Month_PF={$(#=Only(Month))}>}[Planned Finish]) // Second bar

Best regards.

[#4]

Hi Oswaldo,

Thanks for Reply, Actually my requirement is i want to get Month field from Planned Start, Planned Finish, Actual Start, Actual Finish. If i click on july then it should display count of Planned Start, Planned Finish, Actual Start, Actual Finish based on the month.

i tried in many ways but i didn't find the solution. Could you please help me on this.

Thanks in Advance,

MV

[#5]

Hi,

I understand you click on July 2014 need Count for Planned Star in Juliy 2014, Count for Planned Finish in July 2014, same for 2 others Actual Start and Actual Finish, see attached qvw and tell if It is that you need.

Best regards.

[maxgro · Accepted solution]

in this way?

see attachment

[#7]

Hi Oswaldo,

Thank you very very much this is exactly what i want is. But it's showing data only when i select Year and month only.

can we show the data without selecting Year and Month first?

Thank you very much for your input in this issue

Thanks,

MV

[#8]

Thank You very much Massimo,

This is exactly matched with my req.

Thanks,

MV