Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

ytd sales? urgent

pls find attached and i have created a chart and written expression for ytd . pls correct my expression an d tell me what will be the output

for ytd and mtd pls tell me ?

12 Replies
tyagishaila
Valued Contributor

Re: ytd sales? urgent

Hi Manoj,

You have Load table t1 as

t1:

load * inline

[

date,year

1/1/2014,2000

2/1/2014,2001

3/1/2014,2002

4/1/2014,2003

5/1/2014,2004

];

in this there is a date 1/1/2014 and what year column represent as 2000,

do you want to show sum([budget amount]) from date(in table t1) to today's date.

Not applicable

Re: ytd sales?  urgent

Hi,

thanks for sharp reply

can u correct me in the data it self

and i want see here ytd and mtd

pls help me out

i may be wrong with entering data like 1/1/2014  - 2000

pls correct me

tyagishaila
Valued Contributor

Re: ytd sales? urgent

It may be helpful for you,

If(DayNumberOfYear(Date)<=DayNumberOfYear(Today()),1,0) as YTD,

use this flag in expression of your chart

Sum({$<YTD={1}>}[Budget Amount])

This is for Year-to-date analysis,

you would get budget amount of each year from year starting date to today date(17Dec)  of each year.

Not applicable

Re: ytd sales? urgent

Hi,


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: ytd sales?  urgent

pls look into the application give me some idea...

i have seen this in help document

tyagishaila
Valued Contributor

Re: ytd sales? urgent

could you share your actual data and qvd/qvw file?

Not applicable

Re: ytd sales?  urgent

i have attached my file already if not pls check it now

Not applicable

Re: ytd sales?  urgent

Hi

pls find xl sheets and my qvw

i want know how to do ytd and mtd here

tyagishaila
Valued Contributor

Re: ytd sales? urgent

In your original data(your excel file), do you have date attribute?

write flag expression in script and sum expression in chart expression.

You want to calculate YTD(Year-to-date)

it means

if you are comparing your budget of year 2014 to budget of year year 2013.

you can only compare total budget of starting date of year 2013 to till 17dec2013

and  total budget of starting date of year 2013 to  till 17dec2014.

If you are getting anything from it please tell me, so that I can try to clear more.