Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Filter value passing

Hi All

I have reports like

Year ,sum(Sales)

Pre year, Sum(Sales)

My filter is Year.

When i select my filter year = 2016 means my reports should display 2015,total(sales) (means 1 year previuse values).

How can i achieve this in qlik sense.

Thanks

Govind R

12 Replies
arulsettu
Honored Contributor III

Re: Filter value passing

try this

sum({<year={'$(=max(year)-1)'}>}sales)

replace year with your year field

Not applicable

Re: Filter value passing

Showing error arul

sum({<Date(ACT_START_DATE,'MMM-YY')='JUN-12'>}[Grand Total])

'JUN-12' means Date(ACT_START_DATE,'MMM-YY')-1

Testing purpose i given like 'JUN-12'

Error

arulsettu
Honored Contributor III

Re: Filter value passing

create year in backend

year(Date#('JUN-12','MMM-YY')) as year

you will get 2012

then use above expression i gave

MVP
MVP

Re: Filter value passing

Its hard to answer this without knowing how your data is structured in your model. I suggest that you upload a small sample qvw with some representative data to illustrate the problem:

Preparing examples for Upload - Reduction and Data Scrambling

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
galax_allu
Valued Contributor

Re: Filter value passing

Hi

you can use this as well..

sum({<ACT_START_DATE={"$(=Date(Addyears(ACT_START_DATE,-1)),'MMM-YY')"} >}[Grand Total])




if your ACT_START_DATE JUNE-12 you should able to get JUNE-11...!!


Not applicable

Re: Filter value passing

First i want say thanks all

My Date field name is  ACT_START_DATE

This filed having values like

12/8/2016 2:49:00 PM

13/8/2016 SSSSSSS

......

Now in my filter i have to display Month and Year.Thats why i used like below.

Date(ACT_START_DATE, 'MMM-YY')

Now i can able to see my filter values like

Aug-2016

July-2016

Jun-2016

.....

Now if i select Aug-2016 my grand total should display previous one month grand total.

(Later i will implement 1 year before or 1 day before...)

E.g

sum({<Date(ACT_START_DATE, 'MMM-YY')={$(=Date(ACT_START_DATE, 'MMM-YY')-1)}>}[Grand Total])

But above one not working.Can you please help.

Not applicable

Re: Filter value passing

I tried below but not able to see any values

Any issue in my below query

sum({<Date(ACT_START_DATE,'MMM-YY')={$(=date(Addmonths(ACT_START_DATE,-5)),'MMM-YY')} >}[Grand Total])

galax_allu
Valued Contributor

Re: Filter value passing

Hi

try this

sum({<Date(ACT_START_DATE,'MMM-YY')={"$(=date(Addmonths(ACT_START_DATE,-5)),'MMM-YY')"} >}[Grand Total])

or

sum({<Date(ACT_START_DATE,'MMM-YYYY')={"$(=date(Addmonths(ACT_START_DATE,-5)),'MMM-YYYY')"} >}[Grand Total])

Not applicable

Re: Filter value passing

No luck.Testing purpose

can i give =sum({<'Jun-2016'='Jun-2016'>}[Grand Total])

Community Browser