pivot expression get latest months users accordingly

[sunilkumarqv]

Hi ,

please help me on to get attached file expected

three dimension year,head,subhead and Expressions are users

note:please don't  attach qv files i don't have access ..

give some thoughts

[sunilkumarqv]

any thoughts

in a year for a head there are many users for one head need lastest month user ?

[Anonymous]

You dont have Month captured in your excel...how you would get latest month data???

You are having only Year field

In this case take Year, Head, Subhead take as a dimension

Expression: Just write user

[sunilkumarqv]

Hi Balraj,

Month wise data was captured  please look into it input sheet data file .

and also check the scenarios which i explained output sheet data file ..

[sunilkumarqv]

Hi Balraj,

have you checked the file..

[HirisH_V7]

Hi,

May be like this,

Back End:

Temp:
LOAD Year,
    MonthYear,
    If(MonthYear='jan','01/01/'&Year,
      If(MonthYear='feb','01/02/'&Year,
      If(MonthYear='mar','01/03/'&Year,
      If(MonthYear='apr','01/04/'&Year,
      If(MonthYear='may','01/05/'&Year,
      If(MonthYear='jun','01/06/'&Year,
      If(MonthYear='jul','01/07/'&Year,
      If(MonthYear='aug','01/08/'&Year,
      If(MonthYear='sep','01/09/'&Year,
      If(MonthYear='oct','01/10/'&Year,
      If(MonthYear='nov','01/11/'&Year,
      If(MonthYear='dec','01/12/'&Year)))))))))))) as Date,

    Qtr,
    Head,
    subhead,
    user
FROM
[\Data (2).xlsx]
(ooxml, embedded labels, table is [Input data]);




Data:
Load *,
Month(Date) as MonthName,
Num(Month(Date))  as MonthNum
Resident Temp;
Drop  Table Temp;

Front End:

In Pivot Dimensions:

year

Head

Subhead

User

In Pivot expression:

Sum({<MonthNum={'$(=Max(MonthNum))'}>}1)

or

If(Sum({<MonthNum={'$(=Max(MonthNum))'}>}1)=1,user)

Hope this helps,

Regards,

Hirish

[sunilkumarqv]

Thanks for your quick replay

actually get  irrespective of  user names

for example

user not always present in max month

suppose he was present on nov month then we should get nov month user .

not all users should be in dec some user might be in previous month as well in that case your expression wont work .

please look into attached scenarios