topic Re: set analysis in QlikView

set analysis

Tue, 04 Nov 2014 09:58:42 GMT

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

Re: set analysis

alexandros17 — Tue, 04 Nov 2014 10:01:55 GMT

Try something like this:

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

let me know

Re: set analysis

Tue, 04 Nov 2014 10:10:37 GMT

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

Thisis my exp

Re: set analysis

anbu1984 — Tue, 04 Nov 2014 10:17:02 GMT

=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'

Re: set analysis

MK_QSL — Tue, 04 Nov 2014 10:33:16 GMT

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

Re: Re: set analysis

Tue, 04 Nov 2014 12:57:00 GMT

Hi,

kindly check attached file

Regards,

Re: set analysis

MK_QSL — Tue, 04 Nov 2014 13:36:43 GMT

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)

Re: set analysis

Tue, 04 Nov 2014 14:25:12 GMT

HI manish,

its ok but in set analysis

i want eliminate some dates thats why i attached qvw

Re: set analysis

MK_QSL — Tue, 04 Nov 2014 14:27:56 GMT

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

Or do i missed that in your QVW?