topic Re: how to filter date? in QlikView

how to filter date?

shruthibk — Mon, 30 Nov 2015 17:58:29 GMT

Hi,

I got requirement to create 2 charts .where the dimension 'Reporting Date' in one chart it should contain the first day of every month,in another chart the same dimension 'Reporting Date' should contain all the dates.

How can i achieve it in same data model,plz help me to solve this.

thanks,

shruthi

Re: how to filter date?

JonnyPoole — Mon, 30 Nov 2015 18:20:55 GMT

Do you have a field in your data model with all the dates ?

If you do and the field is called DateField

1st chart dimension: DateField

2nd chart dimension: MonthStart(DateField)

Note that the 2nd chart will bucket every measure value from that month and report it under the first day of the month. Do you want that ?

Otherwise i would suggest a monthstart() boolean flag in your data model

load

DateField

if( DateField=MonthStart(DateField), 1, 0 ) as MonthStartFlag

from <>

then you can restrict the rows in your 2nd chart using MonthStartFlag = 1 in set analysis or conditional null suppression

sum( {<MonthStartFlag={0}>} Sales)

sum ( if (MonthStartFlag=0 , Sales) )

etc...

Re: how to filter date?

shruthibk — Mon, 30 Nov 2015 18:27:09 GMT

hi i need to show reporting date in dash board as well like in one dashboard all the reporting dates,in another dashboard only the 1st date of every month

Re: how to filter date?

shruthibk — Mon, 30 Nov 2015 18:29:31 GMT

like i need to provide the reporting date as filter

Re: how to filter date?

maxgro — Mon, 30 Nov 2015 18:55:53 GMT

for first day of the month (only the 1sr) you can use that calulated dimension

=if(Day(repdate)=1, repdate)

Re: how to filter date?

shruthibk — Tue, 01 Dec 2015 03:39:06 GMT

hi my requirement is like i need to show the filtered date in list box for ex.

Reporting date

01/01/10

02/01/10

03/01/10

it is first day of every month date

Re: how to filter date?

shruthibk — Tue, 01 Dec 2015 03:50:25 GMT

Hi thanks this is working fine and my last requirement is i need to show rolling month of 12 months data for 1st day of every month can i go with this approach

sum({<Reporting date={'<=date(monthstart(max(Reporting date))),'MMM YYYY'),>=date(addmonths(monthstart(max(Reporting date)),-12),'MMM YYYY')'>}sales)

is this the right approach?

Re: how to filter date?

jyothish8807 — Tue, 01 Dec 2015 04:57:27 GMT

Hi Shruti,

Try like this:

1. For first date of month.

first create a field in script:

month(Reporting Date) as Month

Create a calculated dimension

if(Reporting Date=min(total<Month> Reporting Date),Reporting Date)

Exp:

sum(Sales)

2. For all dates:

Dimension:

Reporting Date

Exp:

sum(sales)

Hope it helps

Regards

Re: how to filter date?

shruthibk — Tue, 01 Dec 2015 07:26:37 GMT

hi but this will not give rolling 12 months data right

Re: how to filter date?

jyothish8807 — Tue, 01 Dec 2015 09:19:29 GMT

Hi Shruti,

For rolling 12 months, you need to modify your expression and use the same dimension if the requirement is same.

Regards