Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Set analysis

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

Tags (1)
12 Replies
nikhilgarg
Not applicable

Re: Set analysis

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.

Not applicable

Re: Set analysis

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

Not applicable

Re: Set analysis

thank you

Not applicable

Re: Set analysis

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

Thanks

Not applicable

Re: Set analysis

thanks for the reply.

Not applicable

Re: Set analysis

Its alright.

You can mark it correct as correct answer.

If you feel like..

thanks

Not applicable

Re: Set analysis

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

Not applicable

Re: Set analysis

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

Not applicable

Re: Set analysis

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