topic Re: last 3 months using where clause in QlikView

last 3 months using where clause

Wed, 11 Feb 2015 13:50:41 GMT

Hello All,

i have 13 months of data . i would like to restrict last 3 months of data for a specific graph in the chart and also i have a graph where i have to show last 12 months of data .

i would like to restrict data in to qlikview by using where clause to restrict last 12 months of data and 3 months of data at expression for graphs which require trend analysis for 3 months .

Re: last 3 months using where clause

anbu1984 — Wed, 11 Feb 2015 16:39:33 GMT

Check this

Re: last 3 months using where clause

Thu, 12 Feb 2015 04:34:29 GMT

hi anbu , could you please post the logic . i dont have a licenced qlikview version yet?

Re: last 3 months using where clause

PrashantSangle — Thu, 12 Feb 2015 04:46:35 GMT

Hi,

For last 3 months

In set Analysis try like

Sum({<Date={">=$(=Date(Addmonths(max(Date),-3)))"}>}Sales)

for last 12 Months

Sum({<Date={">=$(=Date(Addmonths(max(Date),-12)))"}>}Sales)

Regards

Re: last 3 months using where clause

Thu, 12 Feb 2015 04:59:49 GMT

i have a situation here

the last image i am using expression to filter "not fixed first time" , but i also need to filter last 3 months in this expression now.

so how to add both conditions in set analysis ,

1. to filter not fixed first time "

2. to restrict them for last 3 months

Re: last 3 months using where clause

Thu, 12 Feb 2015 05:01:08 GMT

i have a problem here

the last image i am using expression to filter "not fixed first time" , but i also need to filter last 3 months in this expression now.

so how to add both conditions in set analysis ,

1. to filter not fixed first time "

2. to restrict them for last 3 months

Re: last 3 months using where clause

anbu1984 — Thu, 12 Feb 2015 07:14:55 GMT

Script:

Temp:
Load * Inline [
Year,Month,Sales
2015,2,50
2015,1,100
2014,12,200
2014,11,150
2014,10,100 ];

Final:
Load *,Autonumber(Year+Month) As YrMthNo Resident Temp Order by Year,Month;

Drop table Temp;

Chart:
Dimension: Year,Month
Expr: Sum({<YrMthNo={'$(=Max(YrMthNo))','$(=Max(YrMthNo)-1)','$(=Max(YrMthNo)-2)'}>} Sales)

Re: last 3 months using where clause

jagan — Thu, 12 Feb 2015 07:40:01 GMT

Hi Aditya,

For restricting last 12 months data use below script

LOAD

FROM DataSource

WHERE DateFieldName> MonthStart(Today(), -11) AND DateFieldName <= Today();

In chart to display last 3 months

=Sum({<YearFieldName=, QuarterFieldName=, MonthFieldName=, Date={'>=$(=MonthStart(Today(), -2))<=$(=Today())'}>} Sales)

Hope this helps you.

Regards,

Jagan.

Re: last 3 months using where clause

Thu, 12 Feb 2015 12:32:10 GMT

hi jagan,

i am getting last 11 months data but

for using 3 months data i am unable to get it . i am unable to unds

YearFieldName=, QuarterFieldName=, MonthFieldName=,

i only have monthfieldname = monthesd

dimenions = monthesvd

= enteprise system

i am doing expression on count(enterprise system)

and second question is :

i have a problem here

the last image i am using expression to filter "not fixed first time" , but i also need to filter last 3 months in this expression now.

so how to add both conditions in set analysis ,

1. to filter not fixed first time "

2. to restrict them for last 3 months

Re: last 3 months using where clause

jagan — Fri, 13 Feb 2015 02:25:09 GMT

HI,

Try this for last 3 months

=Sum({<YearFieldName=, QuarterFieldName=, MonthFieldName=, Date={'>=$(=MonthStart(Today(), -2))<=$(=Today())'}>} Sales)

Replace YearFieldName, QuarterFieldName, MonthFieldName fields with your actual Year, Quarter and Month fields.

YearFieldName=, QuarterFieldName=, MonthFieldName= ignores the selections in the Year, Quarter and Month fields, if you do not have any of this fields remove it from the expression. Even if you select one month it always shows last 3 months because we are ignoring the filters.

Use below for Not Fixed First Time and last 3 months

=Sum({<[dfft_Fix Time] = {'Not fixed first time'}, YearFieldName=, QuarterFieldName=, MonthFieldName=, Date={'>=$(=MonthStart(Today(), -2))<=$(=Today())'}>} Sales)

Regards,

Jagan.

Re: last 3 months using where clause

Fri, 13 Feb 2015 05:37:43 GMT

Hi jagan ,

i am getting an error . while using expression .i have month filed only which is monthesvd and datesvd but i am getting this function as in image 1 .

this is the below expression i am using , can you please help me correct my expression?

Re: last 3 months using where clause

PrashantSangle — Fri, 13 Feb 2015 05:41:05 GMT

Hi,

Which filter not fixed at first time??

try like

For last 3 months

In set Analysis try like

Count({<Date={">=$(=Date(Addmonths(max(Date),-3)))<=$(=date(max(Date)))"},[dfft_Fix Time]={"Not fixed first time"}>} [Dfft_Enterprise System])

Regards

Re: last 3 months using where clause

renjithpl — Fri, 13 Feb 2015 06:27:34 GMT

=count({<Monthesvd=, Date={'>=$(=MonthStart(Today(), -2)) <=$(=Today())'}>} [esvd_Enterprise_System])

missed a close bracket..

Re: last 3 months using where clause

Fri, 13 Feb 2015 07:02:21 GMT

hi max ,

i am getting last 4 months of data , i even tried changing from -3 to -2 but it is not changing

please check the images .

Re: last 3 months using where clause

PrashantSangle — Fri, 13 Feb 2015 07:08:22 GMT

Hi,

Debug set analysis by following below steps

1)Covert Chart to straight table.

2)Remove Label of expression and press OK

3)Take your arrow to header of that expression and check the date range it is showing in that expression and modify

the expression as per requirement.

Regards,

Re: last 3 months using where clause

Fri, 13 Feb 2015 07:52:15 GMT

it is working thanq

Re: last 3 months using where clause

Fri, 13 Feb 2015 09:37:56 GMT

hi jagan , after using your expression .

i have many month field names in my excel . right now i am using one specific field at WHERE condition , but some graphs which i am using different month field is giving me last 4 months only . both monthfields are similar and have respective months data in it .