topic Re: Dynamic variables in QlikView

Dynamic variables

ganeshreddy — Tue, 27 Oct 2015 06:08:45 GMT

Hi All,

From the following script i am getting KPI values for september 2015 and october 2015 only because of completion date where condition.How to get july 2015 and august 2015 values as well. Can any one explain how to use dynamic variables (min and max months) for this.

//Code

Summary:

Load *, floor(wo_completionDate)-floor(wo_requestDate) as wo_Diff,

fd_code as [Client source],

year(floor(wo_completionDate)) as CompletionYear,

num(month(floor(wo_completionDate)),'00') as CompletionMonth,

year(floor(wo_createdDate))&'-'&num(month(floor(wo_createdDate)),'00') as CreatedYearMonth,

year(floor(wo_requestDate))&'-'&num(month(floor(wo_requestDate)),'00') as RequestYearMonth,

// year(floor(wo_completionDate))&'-'&num(month(floor(wo_completionDate)),'00') as CompletionYearMonth1

from $(vQVDDataDir)\WO.qvd (qvd) where year(floor(wo_completionDate))='2015'

and num(month(floor(wo_completionDate)),'00') >='07' and num(month(floor(wo_completionDate)),'00')<='10' ;

left join(Summary)

load

CompletionYear,

CompletionMonth,

[Client source],

wo_pk,

count(wo_number) as [No Of Work Orders]

resident Summary

group By

CompletionYear,

CompletionMonth,

[Client source],

wo_pk;

//KPI 1

left join(Summary)

load

CompletionYear,

CompletionMonth,

[Client source],wo_pk,

count(wo_number) as [No Of Work Orders 1]

resident Summary where pri_code= 1 and wc_code = '01'

and wo_Diff <=1

and isnull(proj_number)

and wo_completionDate >= Addmonths(monthStart(Today()),-1) and wo_completionDate <= monthEnd(Today())

group by

CompletionYear,

CompletionMonth,

[Client source]

,wo_pk

;

//KPI 2

left join(Summary)

LOAD

CompletionYear,

CompletionMonth,

[Client source],wo_pk,

count(wo_number) as [No Of Work Orders 1.1]

resident Summary where pri_code= 1 and wc_code = '01'

and isnull(proj_number)

and wo_completionDate >= Addmonths(monthStart(Today()),-1) and wo_completionDate <= monthEnd(Today())

group by

CompletionYear,

CompletionMonth,

[Client source]

,wo_pk

;

Best Regards,

Ganesh

Message was edited by: Ganesh Reddy

Re: Dynamic variables

Gysbert_Wassenaar — Tue, 27 Oct 2015 06:17:31 GMT

If you want two more months then change the -1 to -3 and you can leave out the Addmonths function since Monthstart also accepts an offset parameter:

and wo_completionDate >= monthStart(Today(),-3) and wo_completionDate <= monthEnd(Today())

Re: Dynamic variables

ganeshreddy — Tue, 27 Oct 2015 06:26:54 GMT

Thank for the reply Gysbert, According to the requirement i need to use that where condition with -1 because

Wo_completionDate has to be between the beginning of prior month and the end of current month it is inevitable . The only implementation we can do here is calculating KPIs for each month separately and has add to summary table. Is there any way to use dynamic variable to achive this.

best regards,

Ganesh

Re: Dynamic variables

Gysbert_Wassenaar — Tue, 27 Oct 2015 06:29:39 GMT

The current month is the month of today, i.e. October. You'll never find data from august if the completion date must lie in September.

Re: Dynamic variables

ganeshreddy — Tue, 27 Oct 2015 06:55:57 GMT

Yes Gysbert, because of that i am only getting data for sept and oct. For more reference please find the attached thread. Logic help . I have not got the result there. Here the basic idea is to calculate KPI for the month of July and add it to summary table and then for August and so on...

Best Regards,

Ganesh