topic Showing data dynamically in QlikView

Showing data dynamically

qlikviewwizard — Tue, 28 Apr 2015 06:29:05 GMT

Hi All,

I have the data with the below columns as shown in attached file.

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,LASTDATE,GETDATE

I want to show the Salary of the employees in a straight table based on JOB on Quarterwise (Q1,Q2,Q3,Q4 based on GETDATE).

Please help me.

When I select:

Q1 >>> Should display Q1 Salary of the year,

Q2 >>> Should display Q1,Q2 Salary of the year,

Q3 >>> Should display Q1,Q2,Q3 Salary of the year,

Q4 >>> Should display Q1,Q2,Q3,Q4 Salary of the year,

Thanks in advance.

Re: Showing data dynamically

Anonymous — Tue, 28 Apr 2015 07:45:28 GMT

Hi,

You can use below code to populate Qtr and then use this qtr in your expression and filters.

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

COMM,

DEPTNO,

LASTDATE,

date(GETDATE) as GETDATE,

Year((GETDATE)) as [Year],

Month(GETDATE) as [Month],

Date(GETDATE, 'YYYY-MM') as [Year - Month],

'Q' & Ceil(Month(GETDATE) / 3) as [Quarter]

P.S. - change your date format in text file as YYYY/MM/DD.

Thanks

BKC

Re: Showing data dynamically

qlikviewwizard — Tue, 28 Apr 2015 08:06:47 GMT

Hi balkumarchandel,

Thank you for the script.I loaded successfully.

As shown in pic, If I select Q2, SHould show Q1 and Q2 as well.

Could you send me expression?

Re: Showing data dynamically

er_mohit — Tue, 28 Apr 2015 09:25:17 GMT

Attached herewith sample data.

Hope it helps

Thanks

Mohit

Re: Showing data dynamically

ankitaag — Tue, 28 Apr 2015 10:29:50 GMT

Hi,

Refer the attached application.

Thanks

Re: Showing data dynamically

Anonymous — Tue, 28 Apr 2015 10:32:01 GMT

can you share your sample application as in text file date format is not correct.

Re: Showing data dynamically

qlikviewwizard — Tue, 28 Apr 2015 10:41:52 GMT

Hi balkumarchandel,

Please find the attached file.

AnkitaAg‌, ER.Mohit‌: ! Thank you for the sample files. But it is not I am looking for.

Re: Showing data dynamically

Anonymous — Tue, 28 Apr 2015 11:58:37 GMT

Hi ,

you need to load a data island for selection column .

See attached qvw, Hope it will help you

Re: Showing data dynamically

qlikviewwizard — Tue, 28 Apr 2015 12:12:11 GMT

Thank you balkumarchandel chandel

Re: Showing data dynamically

Anonymous — Tue, 28 Apr 2015 12:29:11 GMT

ur welcome

Re: Showing data dynamically

qlikviewwizard — Thu, 30 Apr 2015 02:32:35 GMT

Hi All,

my report should show dynamically Q1,Q2,Q3,Q4 dynamically. But need to sum of measures

If I select Q1>>>>> should show Q1: Sal,Comm,% (Comm/Sal)

If I select Q2>>>>> should show Q1: Sal,Comm,% (Comm/Sal),Q2:Sal,Comm,% (Comm/Sal) ,Q1+Q2,

If I select Q3>>>>> should show Q1: Sal,Comm,% (Comm/Sal), Q2:Sal,Comm,% (Comm/Sal) ,Q1+Q2, Q1+Q2+Q3,

If I select Q4>>>>> should show Q1: Sal,Comm,% (Comm/Sal), Q2:Sal,Comm,% (Comm/Sal) ,Q1+Q2, Q1+Q2+Q3,Q1+Q2+Q3+Q4

Please help me.

Re: Showing data dynamically

jagan — Thu, 30 Apr 2015 03:45:33 GMT

Hi,

Try like this

EMP:

LOAD

EMPNO,

ENAME,

JOB,

MGR,

date(HIREDATE,'DD-MMM-YYYY') as HIREDATE,

SAL,

COMM,

DEPTNO,

LASTDATE,

Date(GETDATE, 'DD-MMM-YYYY') as GETDATE,

Year(GETDATE) as Year,

month(GETDATE) as Month,

num(Month(GETDATE)) as MonthNo,

Ceil(month(GETDATE)/3) as QtrNo,

'Q'& Ceil(month(GETDATE)/3) as Quarter,

if(GETDATE > LASTDATE, MGR, ENAME) As [Emp - Current]

FROM

EMP.qvd

(qvd);

Remove all the remaining script.

Now Create a variable

vStartDate = Date(YearStart(Max(GETDATE)), 'DD-MMM-YYYY')

vMaxDate = Date(Max(GETDATE), 'DD-MMM-YYYY')

Now in set analysis use this expression

=Sum({<Year=, Month=, Quarter=, GETDATE={'>=$(=vStartDate)<=$(=vMaxDate)'}>} SAL)

Hope this helps you.

Regards,

Jagan.