Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

last 3 months using where clause


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 .

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

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

missed a close bracket..

View solution in original post

16 Replies
Highlighted
Master III
Master III

Check this

Highlighted
Not applicable

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

Highlighted

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

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Not applicable

i have a situation here

Capture1.PNGCapture2.PNG

capture3.PNG

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

Highlighted
Not applicable

i have a problem here

Capture1.PNGCapture2.PNG

capture3.PNG

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

Highlighted
Master III
Master III

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)

Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
Not applicable

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

Capture1.PNGCapture2.PNG

capture3.PNG

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

Highlighted
MVP & Luminary
MVP & Luminary

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.