topic Re: Issue ? Urgent in QlikView

Issue ? Urgent

Fri, 28 Feb 2014 10:25:23 GMT

I Have 30 records, from 1-jan -2014 to 28-feb-2014.

but I want to retreive records which r present between 10-jan-2014 to 5-feb-2014, how can i get those records ?

1. Using Set Analysis

2. With out using Set Analysis

Thanks,

Helen

Re: Issue ? Urgent

kumarnatarajan — Fri, 28 Feb 2014 10:28:43 GMT

Hi,

Using Set Anlysis

sum( {$<Date= {“>=10-jan-2014 <=5-feb-2014”}>} Sales )

without Set Anlysis

Sum(if(Date>='10-jan-2014' and Date<='5-feb-2014',Sales))

Re: Issue ? Urgent

Fri, 28 Feb 2014 10:37:23 GMT

If I want to Select dynamically the dates then ?

Re: Issue ? Urgent

Fri, 28 Feb 2014 10:37:28 GMT

1) With set analysis:

Create 2 variables:

v_DateStart=num(10-jan-2014)

v_DateEnd=num(5-feb-2014)

sum( {$<Date= {“>=$(v_DateStart) <=$(v_DateEnd)”}>} Sales )

2) Without set analysis:

use a custom dimension in chart or table like this:

if(Date>=$(v_DateStart) and Date<=$(v_DateEnd), Date)

and your expression (ex. sum(1))

Hope this helps

Re: Issue ? Urgent

kumarnatarajan — Fri, 28 Feb 2014 10:41:50 GMT

Hi,

use min, max function for dynamic

sum( {$<Date= {“>= $(=Min(Date)) <= $(=Max(Date))”}>} Sales )

Re: Issue ? Urgent

Fri, 28 Feb 2014 10:46:41 GMT

so you can use a list box to select dynamically your date and the you graphic react with your selection

Re: Issue ? Urgent

sunilkumarqv — Fri, 28 Feb 2014 10:52:58 GMT

Hi helen

I did this in my project its worked try these

Use in script

set dataformat='DD/MM/YYYY';

Let MinDate='10-01-2014 ';

Let MaxDate=' 5-2-2014';

use these in experssion

= sum({<num(Data)={">=$(MinDate)<=$(MaxDate)"}>} sales)

Re: Issue ? Urgent

Fri, 28 Feb 2014 11:06:36 GMT

Thanks All,

If I want to get Last Six months of Data & In the Same way for previous year Same Six Months Data.

Comparison:

Last Six Months: Sep'13 to Feb'14

Last Year Six Months: Sep'12 to Feb'13

Regards,

Helen

Re: Issue ? Urgent

Anonymous — Fri, 28 Feb 2014 11:26:05 GMT

Hi Helen,

You can retrieve record between specific dates using Set analysis and Script.

Implement master calender in script,its more flexible and user interval match() function.

Which gives more performance.

Re: Issue ? Urgent

Fri, 28 Feb 2014 11:33:13 GMT

Hi Helen,

Time chat wizard may help you. You can find it in tools menu. I am new to qlikview and may can't give an example.

thanks,

madhu

Re: Issue ? Urgent

Fri, 28 Feb 2014 11:34:46 GMT

Thanks Nitha..

Could you please give me an example related that...

Re: Issue ? Urgent

Anonymous — Fri, 28 Feb 2014 11:54:20 GMT

Implement for master calendar:

Let vMin = num(makedate(2014,01,10));
Let vMax = num(makedate(2014,02, 05));

Temp:
Load Date($(vMin) +RowNo() -1) as DATE
AutoGenerate 1
While Date($(vMin) +RowNo() -1) <Date($(vMax));

Master_cal:
Load DATE,
Day(DATE) as DAY,
if(Day(DATE) >=10,Month(AddMonths(DATE,1)), Month(DATE)) as MONTH,
if(Day(DATE) >=10,Year(AddMonths(DATE,1)), Year(DATE))as YEAR
Resident Temp;

Drop table Temp;

Re: Issue ? Urgent

linusblomberg — Fri, 28 Feb 2014 11:54:28 GMT

I think you need to specify what functionality you are looking for if we will be able to help. How to write the set analysis statement has been written and so on. Do you want for a user to choose a date and then see 6 months back?

I usually have a bunch of date variables defined to use in my set analysis expressions. For example

vMaxDate max(date)

vMaxDateLY addyears(vMaxDate,-1)

vDateR6 addmonths(vMaxDate,-6) and so on. You could add a monthstart in there to show complete months.

Hope this helps

Re: Issue ? Urgent

Fri, 28 Feb 2014 12:12:42 GMT

Thanks Nitha,

I am asking for last 6 months & last to last 6 Months...