Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set analysis

Hi,

Table:

name     sales     data

1          200          12-01-2001

2          300          21-01-20001

from till date.

I want ytd expression. but in this expression i dont want some dates  like jan26, aug15, and some other dates.

---

Regards

K

8 Replies
alexandros17
Partner - Champion III
Partner - Champion III

Try something like this:

Sum({$ <data={'*'}-{'26/08/2014'}>} Values)

let me know

Not applicable
Author

=sum({<Year = {$(vYTDYear)},[Date ID]={">=$(vStartYTD)<=$(vToday)"}>}Value)

Thisis my exp

anbu1984
Master III
Master III

=sum({<Year = {$(vYTDYear)},[Date ID]={">=$(vStartYTD)<=$(vToday)"},[Date ID]-={'26/01/2014','15/08/2014'}>}Value)


Make sure format of [Date ID] matches with '26/01/2014'

MK_QSL
MVP
MVP

Can you provide some more data please? Looks like you want to ignore the dates having public holidays...

Not applicable
Author

Hi,

kindly check attached file

Regards,

k

MK_QSL
MVP
MVP

For 1st Bar Chart

Create a Bar Chart without any Dimension

Expression1

Label

='YTD(' &YearStart(Max({<Year = {'$(=Max(Year))'}>}[Date ID]))& '-' & Max({<Year = {'$(=Max(Year))'}>}[Date ID]) &')'

Definition

=SUM({<Year = {'$(=Max(Year))'}, [Date ID] = {"<=$(=Max({<Year = {'$(=Max(Year))'}>}[Date ID]))"}>}Value)

Expression2

Label

='YTD(' &YearStart(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1)& '-' & AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1) &')'


Definition

=SUM({<Year = {'$(=Max(Year)-1)'}, [Date ID] = {"<=$(=AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1))"}>}Value)

=================================================================

For 2nd Bar Chart

Create a Bar Chart without any Dimension

Expression1

Label

='YTD(' &Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1))& '-' & Max({<Year = {'$(=Max(Year))'}>}[Date ID]) &')'


Definition

=SUM({<[Date ID] = {">=$(=Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1)))<=$(=Max({<Year = {'$(=Max(Year))'}>}[Date ID]))"}>}Value)

Expression2

Label

='YTD(' &Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-2))& '-' & Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1)) &')'

Definition

=SUM({<[Date ID] = {">=$(=Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-2)))<=$(=Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1)))"}>}Value)

Not applicable
Author

HI manish,

its ok but in set analysis

i want eliminate some dates thats why i attached qvw

MK_QSL
MVP
MVP

You nowhere in QVW mentioned about elimination of any dates....

Or do i missed that in your QVW?