need help

[#1]

I want to get last year saleQty and the this year saleQty by selecting a year and a month. Financial year begins from April. i m getting last year saleQty for a month like this.

Num((SUM({<year = {'$(=Max(year)-1)'}>}saleQty)),'#,##0')

but now i want to get saleQty for a period(April to this month) for last year and this year. pls help

[jagan]

Hi Dushan,

Can you attach some sample data in file, so that it would be easier to give solution.

Regards,

jagan.

[PradeepReddy]

If you select the year '2010', month 'Feb', the below expression will give you the result for "Sum of the sales in month of Feb in both the years 2010 & 2009"

=sum({<Year={$(=Year),$(=Year-1)},Month={$(=Month)}>}Sales)

Hope this will help you.

Thanks, Pradeep

[#4]

Hi,

pls find the attached copy

[#5]

pls find the attached copy

[manojkulkarni]

As mentioned by Jagan, you could create date using month & year available in your model. Once date field is available then it is easy to make any kind of selections. You can even create flags in data model for YTM & LYTM, which will be helpfull in making charts.

Using InMonth() fn of Qlikview, you can create YTM,LYTM etc..

[sunilkumarqv]

First you need to create one Date Field without That  not make it work

Create Date Field  like these and use in expression as of  jagan

Load

Date(month) as Date

resident tablename;

[jagan]

Hi,

Use this script

sale:

LOAD SALAMDDDT,

     SALAMDTIME,

     SALAMDUCD,

     SALCAT as category,

     SALDDT,

     SALDM,

     SALFLG,

     SALGWRQTY as growthQty,

     SALGWRVAL as growthVal,

     SALMON as month,

     SALQTY as saleQty,

     SALQUOSALQTY,

     SALQUOSALVAL,

     SALRGN,

     SALRMARK,

     SALRUTID as location2,

     SALSITE as location1,

     SALSYSTGQTY,

     SALSYSTGVAL,

     SALTGCALDDT,

     SALTGCALTIME,

     SALTGCALUSD,

     SALTGQTY as targetQty,

     SALTGVAL as targetVal,

     SALTWN,

     SALUPR,

     SALVAL as saleVal,

     SALYEAR as year,

     MakeDate(SALYEAR, SALMON) AS SalDate

     if(Left(SALSITE,1)='S' or Left(SALSITE,1)='M','Retail','WholeSale') as mainChannel

FROM

(qvd);

And use the above expressions.

For Previous Year YTM:

SUM({<month=,  year=, Date={'>=$(=YearStart(Today(), -1, 4))<=$(=AddMonths(Today(), -12))'}>}saleQty)

For Current Year YTM:

SUM({<month=,  year=, Date={'>=$(=YearStart(Today(), 0, 4))<=$(=Today())'}>}saleQty)

Hope this helps you.

Regards,

Jagan.

[PrashantSangle]

Hi

Addition to the this script create Fiscal Year

use this in your script

if(num(Month(SalDate))<=3,Year(TempDate)-1,Year(TempDate)) as FiscalYear,

and the use this expression

CY YTM:

sum({$<SalDate={"$(=MonthName(max(SalDate)))"}>} Sales)

LY YTM:

sum({$<SalDate={"$(=MonthName(addYears(Max(SalDate),-1)))"}>} Sales)

[PrashantSangle]

Hi,

change this to above

if(num(Month(SalDate))<=3,Year(SalDate)-1,Year(SalDate)) as FiscalYear

[#11]

Hi jagan, your method is working. but when I select a year and month figure should change to selecting year and month. pls help