topic Re: Set analysis in QlikView

Set analysis

Tue, 16 Dec 2014 06:45:17 GMT

Hi guys,

i have year,quarter and month fields in my data source. I need set expression for the below cases:

1. current year vs last year

2. Current year quarter vs last year same quarter. For Ex: FY14Q1 vs FY13Q1

3. Current Quarter vs previous quarter of same year

thanks

Re: Set analysis

nikhilgarg — Tue, 16 Dec 2014 06:49:31 GMT

If you want to find the sum of sales then you can do like:

Take bar chart and then in expression:

Sum({<Year = {Max(Year)}>}Sales) // For current year.

Sum({<Year = {Max(Year)-1}>}Sales) // For last year.

And likewise other expressions. And also see to the syntax.

Re: Set analysis

Tue, 16 Dec 2014 06:49:49 GMT

Hi Viresh,

This might help.

YTD (Year-To-Date) Sales:
- Sum({$<MonthID = {“<=$(=Max(MonthID))”},
  Year = {$(=Max(Year))},
  Quarter = ,
  Month = >} Sales)
QTD (Quarter-To-Date) Sales:
- Sum({$<MonthID = {“<=$(=Max(MonthID))”},
  QuarterID = {$(=Max(QuarterID))},
  Year = ,
  Quarter = ,
  Month = >} Sales)
MTD (Month-To-Date) Sales:
- Sum({$<MonthID = {$(=Max(MonthID))},
  Year = ,
  Quarter = ,
  Month = >} Sales)
Previous Month Sales:
- Sum({$<MonthID = {$(=Max(MonthID) – 1)},
  Year = ,
  Quarter = ,
  Month = >} Sales)
Previous Quarter Sales:
- Sum({$<QuarterID = {$(=Max(QuarterID) – 1)},
  Year = ,
  Quarter = ,
  Month = >} Sales)
Sales for the same Month but Previous Year:
- Sum({$<MonthID = {$(=Max(MonthID) – 12)},
  Year = ,
  Quarter = ,
  Month = >} Sales)
Sales for same Quarter of the Previous Year:
- Sum({$<QuarterID = {$(=Max(QuarterID) – 4)},
  Year = ,
  Quarter = ,
  Month = >} Sales)
YTD Sales for Previous Year
- Sum({$<MonthID = {“<=$(=Max(MonthID) – 12)”},
  Year = {$(=Max(Year) – 1)},
  Quarter = ,
  Month = >} Sales)
Sales for Rolling 12 Months:
- Sum({$<MonthID = {“>=$(=Max(MonthID) – 11)<=$(=Max(MonthID))”},
  Year = ,
  Quarter = ,
  Month = >} Sales)

Thanks

Sudhanshu shrivas

Re: Set analysis

Tue, 16 Dec 2014 07:00:56 GMT

thank you

Re: Set analysis

Tue, 16 Dec 2014 07:04:14 GMT

Please close the thread If you get it what you wanted by marking it correct .

Thanks

Re: Set analysis

Tue, 16 Dec 2014 07:06:34 GMT

thanks for the reply.

Re: Set analysis

Tue, 16 Dec 2014 07:15:22 GMT

Its alright.

You can mark it correct as correct answer.

If you feel like..

thanks

Re: Set analysis

Tue, 16 Dec 2014 07:15:45 GMT

Hello S Shrivas,

Below expr :

Sum({$<MonthID = {“>=$(=Max(MonthID) – 11)<=$(=Max(MonthID))”},
Year = ,
Quarter = ,
Month = >} Sales)

May I know that, here Year =, Quarter =, Month =,. what we would get here.

Thanks,

Ananth

Re: Set analysis

Tue, 16 Dec 2014 07:24:33 GMT

hi,

This is for sales for rolling 12 months.

and by doing

Year = ,

Quarter = ,

Month =

we are bypassing it,from the expression.

thanks

sudhanshu shrivas

Re: Set analysis

Tue, 16 Dec 2014 08:20:17 GMT

Means, these will select all Year, Quarter and Months right?

Re: Set analysis

Tue, 16 Dec 2014 08:43:29 GMT

hi,

yeah as we are by passing.It wil go by the set expression.

http://community.qlik.com/docs/DOC-4252

this will help u understanding rolling months properly.

Thanks

sudhanshu shrivas

Re: Set analysis

Mon, 19 Jan 2015 07:28:21 GMT

Hi Shrivas,

I am having the same problem of calculating the dates and I have used the expression you have just pasted but I am not getting any result....it populates zeros on the fields and dashes on other fields like LYTD and YTD. is the anything that I am doing it wrong? below is the expressions that I have created:

MTD:

Sum({$<MonthId = {$(=Max(MonthId)– 1)}, Year = , Quarter = , Month = >} Counter_Num)

PMTD:

Sum({$<MonthId = {$(=Max(MonthId) – 1)},Year = ,Quarter = ,Month = >} Counter_Num)

Prior Year MTD:

Sum({$<MonthId = {$(=Max(MonthId) – 12)},Year = ,Quarter = ,Month = >} Counter_Num)

YTD:

Sum({$<MonthId = {“<=$(=Max(MonthId))”},Year = {$(=Max(Year))},Quarter = ,Month = >} Counter_Num)

LYTD:

Sum({$<MonthID = {“<=$(=Max(MonthID) – 12)”},Year = {$(=Max(Year) – 1)},Quarter = ,Month = >} Counter_Num)

QTD:

Sum({$<MonthID = {“<=$(=Max(MonthID))”}, QuarterID = {$(=Max(QuarterID))},
Year = ,
Quarter = ,
Month = >} Counter_Num)

PrevYear_QTD:

Sum({$<QuarterID = {$(=Max(QuarterID) – 4)},
Year = ,
Quarter = ,
Month = >} Counter_Num)

Re: Set analysis

Mon, 19 Jan 2015 07:57:10 GMT

Hi Guys,

can anyone help please.....