topic Re: dispaly total year in QlikView

dispaly total year

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a data for fiscal year full Aprl to march

in my pivot table am showing plan and actual for current month

but my requirement is that I have a plan values total fiscal year but I have actual values till current month only

I want to show all the plan values total fiscal year and actual should show zero.

using this expression now : sum({<Fiscal Year]={$(=max([Fiscal Year]))}, MonthNo = {"<=$(=max(MonthNo))"},[Month Name]=,Month=>} [PAmount])

from above I want to show total data not for current month

Re: dispaly total year

Anil_Babu_Samineni — Tue, 02 Jan 2018 07:20:12 GMT

Try this way?

If(MonthNo <> Max(TOTAL MonthNo), sum(TOTAL {<Fiscal Year]={$(=max([Fiscal Year]))}, MonthNo = {"<=$(=max(MonthNo))"},[Month Name]=,Month=>} [PAmount]), sum({<Fiscal Year]={$(=max([Fiscal Year]))}, MonthNo = {"<=$(=max(MonthNo))"},[Month Name]=,Month=>} [PAmount]))

Re: dispaly total year

soniasweety — Tue, 02 Jan 2018 07:34:39 GMT

thanks anil,

but I am getting 2016 to oct2017 but I need 2017arpl to 2018 mar

and for your expression all months plan data showing same number

Re: dispaly total year

Anil_Babu_Samineni — Tue, 02 Jan 2018 07:36:16 GMT

Not sure, I followed you. But to answer i would suggest to share some inputs related monthno? How it looks like.

Re: dispaly total year

soniasweety — Tue, 02 Jan 2018 07:41:14 GMT

here it is

Re: dispaly total year

Anil_Babu_Samineni — Tue, 02 Jan 2018 07:58:21 GMT

That means Monthno doesn't have Months. First build the Fiscal Year and then use only Fiscal Fields to get into work

Re: dispaly total year

kamalqlik — Tue, 02 Jan 2018 08:08:10 GMT

Hi Sony,

First you have to create Fiscal year at backend and then use the expression which Anil have shared

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
          *;
Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year
         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month
          *;
Load Year(MonthNo)                              as Year,           // Your standard master calendar
         Month(MonthNo)                            as Month,
       resident your table containing monthno or date

Re: dispaly total year

soniasweety — Tue, 02 Jan 2018 08:49:15 GMT

we don't have master calendar

am using this in script for creating MonthNo

"Month Name",

Num(MonthName("Month Name")) as MonthNo,

Month Name values are apr 2016 may 201 6 til mar 2018 like that

fiscal year is derived like below

if(left("Month Name",3)='Jan' or left("Month Name",3)='Feb' or left("Month Name",3)='Mar',

Right("Month Name",4)-1,Right("Month Name",4)) as "Fiscal Year",

Re: dispaly total year

kamalqlik — Tue, 02 Jan 2018 12:13:46 GMT

Hi Sony,

You can use updated script as per my comment above :

Num(MonthName("Month Name")) as MonthNo

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
          *;
Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year
         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month
          *;
Load Year(Month Name)                              as Year,           // Your standard master calendar
         Month(Month Name)                            as Month,

Num(MonthName("Month Name")) as MonthNo
resident your table containing monthno or date

Then you can use the expression as per above case.

Re: dispaly total year

kamalqlik — Wed, 31 Jul 2019 21:09:01 GMT