Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
soniasweety
Valued Contributor III

dispaly total year

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

Tags (2)
9 Replies

Re: dispaly total year

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]))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
soniasweety
Valued Contributor III

Re: dispaly total year

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
soniasweety
Valued Contributor III

Re: dispaly total year

here it ismnth.PNG

Re: dispaly total year

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: dispaly total year

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

soniasweety
Valued Contributor III

Re: dispaly total year

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",

Partner
Partner

Re: dispaly total year

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.

Partner
Partner

Re: dispaly total year

PFA