topic Re: null value in selected year vs its previous year in QlikView

null value in selected year vs its previous year

Anonymous — Sun, 30 Jul 2017 08:57:19 GMT

Hi there,

I have a question about month over month comparison. What I want to do is to compare the total sales amount between selected fiscal year and the previous year of the selected fiscal year, and then further to calculate growth %.

However, I found that if there is null value in some of the months in selected fiscal year, the total sales amount of this months in its previous fiscal year will be ignored in the calculation. For the example in attached qvw, if Client C has no sales in April and June FY17, the sales amt of April and June in FY16 will be ignored. Please see the set analysis below.

Total Amt in FY16 =

SUM({<[Fiscal Year Text]=,[Fiscal Year] = {$(=MAX([Fiscal Year])-1)},[Fiscal Month] = p([Fiscal Month]) >} [Amt])

** April and June sales amt are missed in this set analysis.

If I add {1} to possible function, it will include all months. But for the current fiscal year FY18 (fiscal year start at 1 July), I want to show July data only in the table. It seems the {1} not work for current fiscal year.

SUM({<[Fiscal Year Text]=,[Fiscal Year] = {$(=MAX([Fiscal Year])-1)},[Fiscal Month] = p({1} [Fiscal Month]) >} [Amt])

So, may I ask how can I get the exact total amt for selected year and its previous year?

Expected result:

If select FY18:

total sales amt in FY18: 310

total sales amt in FY17: 310

If select FY17:

total sales amt in FY17: 3050

total sales amt in FY16: 3660 (include April and June data)

Thank you.

Re: null value in selected year vs its previous year

antoniotiman — Sun, 30 Jul 2017 10:27:39 GMT

Hi Nicole,

try this

SUM({<[Fiscal Year Text]=,[Fiscal Year] = {$(=MAX([Fiscal Year])-1)},
[Fiscal Month]={'>=$(=Min([Fiscal Month]))<=$(=Max([Fiscal Month]))'}>} [Amt])

Regards,

Antonio

Re: null value in selected year vs its previous year

lironbaram — Sun, 30 Jul 2017 10:32:44 GMT

use this expression for FY16

SUM({<[Fiscal Year Text]=,[Fiscal Year] = {$(=MAX([Fiscal Year])-1)},[Fiscal Month] = p({<[Fiscal Year Text]=,[Fiscal Year] = {'$(=MAX([Fiscal Year])-1)'}>}[Fiscal Month]) >} [Amt])

Re: null value in selected year vs its previous year

Anonymous — Mon, 31 Jul 2017 09:41:18 GMT

Hi Antonio, Liron,

Thanks for your solutions. But i'm afraid I got a mistake when creating this testing QVW (I put an incorrect order to Fiscal Month). So, the issue is still here when I use the set analysis in the revised QVW.

Re: null value in selected year vs its previous year

sunny_talwar — Mon, 31 Jul 2017 12:03:45 GMT

May be try this

SUM({<[Fiscal Year Text]=,[Fiscal Year] = {$(=MAX([Fiscal Year])-1)},[Fiscal Month] = {"$(='<=' & Max([Fiscal Month]))"}>} [Amt])

When FY17 is selected

When FY18 is selected

Re: null value in selected year vs its previous year

Anonymous — Tue, 01 Aug 2017 01:27:05 GMT

Hi Sunny,

Thanks for your sharing. It seems June data is missing. I think this is because max fiscal month in FY17 is May, so it return May as max fiscal month in set analysis.

Regards,

Nicole

Re: null value in selected year vs its previous year

sunny_talwar — Tue, 01 Aug 2017 02:07:32 GMT

May be create a master calendar to handle this issue