topic Re: need help in QlikView

need help

Thu, 13 Mar 2014 12:01:43 GMT

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

Re: need help

Thu, 13 Mar 2014 13:21:49 GMT

is anyone have a solution?

Re: need help

jagan — Fri, 14 Mar 2014 02:05:17 GMT

Hi Dushan,

If you have date field in your data model then try like this

For Previous Year:

SUM({<Month=, Quarter=, Year=, Date={'>=$(=YearStart(Today(), -1, 4))<=$(=YearEnd(Today(), -1, 4))'}>}saleQty)

For Current Year:

SUM({<Month=, Quarter=, Year=, Date={'>=$(=YearStart(Today(), 0, 4))<=$(=YearEnd(Today(), 0, 4))'}>}saleQty)

Regards,

Jagan.

Re: need help

Fri, 14 Mar 2014 02:53:25 GMT

Hi Jagan, I only have year and month field in my model

Re: need help

jagan — Fri, 14 Mar 2014 03:05:46 GMT

Hi,

Arrive the Date field like this

LOAD

MakeDate(Year, Month) AS DateFieldName

Regards,

Jagan.

Regards,

Jagan.

Re: need help

Fri, 14 Mar 2014 03:33:18 GMT

hi jagan, expression is not working

Re: need help

er_mohit — Fri, 14 Mar 2014 04:12:41 GMT

Try this

For YTD

Sum({< Datefield = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'},FiscalYear=,Month= >} saleQty )

here my two variable

vStartDate =MonthStart(Date(min(Datefield)))

vEndDate =Date(Max(Datefield))

for LYTD

Sum({<Datefield={'>=$(vPYStartDate)<=$(vPYEndDate)'},FiscalYear=, Month=>}saleQty )

here my two variable

vPYStartDate =AddMonths('$(vStartDate)',-12)

vPYEndDate =AddMonths('$(vEndDate)',-12)

hope it helps

Re: need help

Fri, 14 Mar 2014 04:35:19 GMT

hi all, I need YTM

Re: need help

Fri, 14 Mar 2014 04:41:39 GMT

Hi, I have year, month and Sum(saleQty) columns only. I want to get YTM and LYTM from April(bcoz it s the first month of financial year)

Re: need help

Fri, 14 Mar 2014 04:48:07 GMT

Hi Mohit, How can I change this to YTM and LYTM. because I only have year and month columns , no date column in my database. pls tell me how can i do this using year and month

Re: need help

jagan — Fri, 14 Mar 2014 05:07:17 GMT

Hi Dushan,

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

Regards,

jagan.

Re: need help

PradeepReddy — Fri, 14 Mar 2014 05:19:54 GMT

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

Re: Re: need help

Fri, 14 Mar 2014 07:09:47 GMT

Hi,

pls find the attached copy

Re: Re: Re: need help

Fri, 14 Mar 2014 07:15:40 GMT

pls find the attached copy

Re: need help

manojkulkarni — Fri, 14 Mar 2014 07:22:48 GMT

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..

Re: need help

sunilkumarqv — Fri, 14 Mar 2014 07:29:03 GMT

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;

Re: need help

jagan — Fri, 14 Mar 2014 07:31:32 GMT

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.

Re: need help

PrashantSangle — Fri, 14 Mar 2014 07:39:52 GMT

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)

Re: need help

PrashantSangle — Fri, 14 Mar 2014 07:45:26 GMT

Hi,

change this to above

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

Re: Re: Re: need help

Fri, 14 Mar 2014 08:51:26 GMT

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