topic Re: Problem to get month from multiple fields in QlikView

Problem to get month from multiple fields

Thu, 24 Jul 2014 13:46:08 GMT

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,

Re: Problem to get month from multiple fields

Thu, 24 Jul 2014 15:50:01 GMT

Hi All,

Any ideas plzzzzzz, it's an urgent req

Thanks,

Re: Problem to get month from multiple fields

Thu, 24 Jul 2014 16:40:22 GMT

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.

Re: Problem to get month from multiple fields

Thu, 24 Jul 2014 17:09:35 GMT

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,

Re: Re: Problem to get month from multiple fields

Thu, 24 Jul 2014 17:15:24 GMT

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.

Re: Problem to get month from multiple fields

maxgro — Thu, 24 Jul 2014 18:15:39 GMT

in this way?

see attachment

Re: Problem to get month from multiple fields

Thu, 24 Jul 2014 18:30:05 GMT

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,

Re: Problem to get month from multiple fields

Thu, 24 Jul 2014 18:55:15 GMT

Thank You very much Massimo,

This is exactly matched with my req.

Thanks,