topic Re: pivot expression get latest months users accordingly in QlikView

pivot expression get latest months users accordingly

sunilkumarqv — Fri, 15 Jan 2016 06:22:14 GMT

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

Re: pivot expression get latest months users accordingly

sunilkumarqv — Fri, 15 Jan 2016 07:42:56 GMT

any thoughts

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

Re: pivot expression get latest months users accordingly

Anonymous — Fri, 15 Jan 2016 08:16:16 GMT

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

Re: pivot expression get latest months users accordingly

sunilkumarqv — Fri, 15 Jan 2016 09:06:33 GMT

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

Re: pivot expression get latest months users accordingly

sunilkumarqv — Sat, 16 Jan 2016 06:46:14 GMT

Hi Balraj,

have you checked the file..

Re: pivot expression get latest months users accordingly

HirisH_V7 — Sat, 16 Jan 2016 07:35:40 GMT

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)

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

Hope this helps,

Regards,

Hirish

Re: pivot expression get latest months users accordingly

sunilkumarqv — Sat, 16 Jan 2016 09:42:39 GMT

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