topic Re: YTD Sales analysis Calculation in QlikView

YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 05:22:55 GMT

Hello,

I was trying to show the sales analysis for Fiscal year that shows from Apr to Mar. When I select Customer, Year and Month the sum of sales shows correctly. However If I select just Year, the sales amount shows way over.

The Expression formula that shows in text object looks like below :

=NUM(Sum({<Fyear={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,FMonth=>}(History_Shipments * Price_Price)),'$ #,##0', '.',',')

=NUM(sum({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'},Day={'<=$(vMaxDay)'},Date=,FMonth=>}(History_Shipments * Price_Price)),'$ #,##0', '.',',')

One thing to mention, this formulas gives me correct answers when I do it on straight table. This happens only on Text object as the requirement is to display on text object.

Can anybody please help?

Thnaks,

Wossen

Re: YTD Sales analysis Calculation

MK_QSL — Sun, 11 Dec 2016 08:58:38 GMT

What is the format of Fyear?

Can you provide sample app?

Re: YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 09:10:13 GMT

Manish - this is how it looks on the script of my calendar:

Set vFM = 4 ; // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

YearToDate(PeriodDate,0,$(vFM)) as FYTDFlag,

LOAD DayID,

PeriodDate,

Floor(PeriodDate) AS DateNum, //it will gives you one whole number to represent date YTD - Year To Date

Year(PeriodDate) as Year,

Date(PeriodDate) as Date,

Month(PeriodDate) as Month,

Date(monthstart(PeriodDate), 'MMM-YYYY') as MonthYear,

Day(PeriodDate) as Day,

Day(PeriodDate) as Week,

num(year(PeriodDate))*100+num(month(PeriodDate)) as year_month_num,

'Q' & ceil(month(PeriodDate)/3) as QTY,

WeekID,

WeekStartDate,

WeekEndDate,

MonthID,

MonthStartDate,

MonthEndDate,

QuarterID,

QuarterStartDate,

QuarterEndDate;

Fyear shows as 2013/2014

Re: YTD Sales analysis Calculation

vinieme12 — Sun, 11 Dec 2016 09:27:59 GMT

which year are your making the selection on Year or FYear

Why don't you consistently use the Numerical Fiscal year field which is 'fYear' in both expressions?

Re: YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 09:47:58 GMT

I am using fYear for Year which shows as 2014, 2015, and FMonth for the month, Apr, May, June...and so on.

It is good advise and I just changed it, but still I don't see any change.

Re: YTD Sales analysis Calculation

vinieme12 — Sun, 11 Dec 2016 10:02:10 GMT

can you upload a sample app

Preparing examples for Upload - Reduction and Data Scrambling

Re: YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 10:21:31 GMT

Here you go.

Re: YTD Sales analysis Calculation

MK_QSL — Sun, 11 Dec 2016 13:35:26 GMT

Why you are using fYear in set expression?

Your Fiscal Year is from April to March so better to use FYear.

Re: YTD Sales analysis Calculation

vinieme12 — Sun, 11 Dec 2016 15:10:07 GMT

Hi Wossen,

The Expression is correct. I just downloaded the data in Excel and verified the same.

The reason Figures don't match when you select all customer's is that in your Data there are a lot of rows which have null() values in "Customer Name" and null values don't show up in list box.

I suggest During load add a validation and add default value for nulls in Customer Name field.

example

if(isnull([Customer Name],"NameNotFound",[Customer Name]) as [Customer Name],

so you can filter for those customers as well.

Re: YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 18:34:11 GMT

Hi Vineeth,

Thanks for looking into this. Yes, there are null values and that is why I have this 'WHERE Len( "Customer Name" ) > 0' on the script under the customer table.

But why I don't see the total YTD sales value on the text object where as when I use the same formula on straight table the amount looks correct.

Any clues?

Thanks,

Wossen

Re: YTD Sales analysis Calculation

vinieme12 — Sun, 11 Dec 2016 18:44:28 GMT

That's because with Suppress Null checked ..you won't see null customer names in straight table so your totals look correct.

Well your data definitely has null values which means your script isn't working as expected you might want to recheck the where clause

where len("Customer Name")>0; and removed the or part

Re: YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 18:51:45 GMT

Vineeth - No the Suppress Null hasn't check on the straight table I have been working on.

Do you happen to know what formula to use in order to exclude the Null values other than what I used ? I am loading the data from SQL.

Re: YTD Sales analysis Calculation

vinieme12 — Sun, 11 Dec 2016 19:04:41 GMT

You can also try

WHERE NOT ISNULL(Customer Name)

Re: YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 20:29:37 GMT

Hi Vineeth ,

Thanks and I applied that one you recommend me and it doesn't like it. But, you are correct there was still a null and blank value for customer and other fields like Molecule. I used WHERE NOT([Customer Name] IS NULL or [Customer Name] = ' ' or Molecule IS NULL or Molecule = ' '); and now this give me a valid data's. However the Total YTD calculation still not right.

Re: YTD Sales analysis Calculation

sunny_talwar — Sun, 11 Dec 2016 21:47:14 GMT

I think right now you are doing a or test between Customer Name and Molecule.... So if either of them is not null, the application will still pick the null value for other. So If for a particular row Molecule is not null, but Customer Name is, then it will be picked. May be you want this?

WHERE Len( "Customer Name" ) > 0 AND Len (Molecule) > 0;

Re: YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 22:00:07 GMT

Hi Sunny - Thanks for your input.

Actually the null and blank are exists in both column. So I used the OR function and excluding them now.

The problem I have is the YTD calculation part. The expression I have is:

SUM({<Fyear={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,FMonth=>}(History_Shipments * Price_Price))

Any thought on this?

Re: YTD Sales analysis Calculation

sunny_talwar — Sun, 11 Dec 2016 22:02:36 GMT

Can you check if this is working?

Sum({<Fyear = {'$(=vMaxYear)'},Date = {'<=$(=vMaxDate)'},Month=,Day=,FMonth=>} (History_Shipments * Price_Price))

Re: YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 22:12:19 GMT

Sunny - It give me incorrect amount like way over

Re: YTD Sales analysis Calculation

sunny_talwar — Sun, 11 Dec 2016 22:14:16 GMT

Can you share an updated sample to look at this issue?

Re: YTD Sales analysis Calculation

wossenhh — Sun, 11 Dec 2016 22:29:29 GMT

Here it is.

Thanks Sunny