Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
soniasweety
Master
Master

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

9 Replies
Anil_Babu_Samineni

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
Master
Master
Author

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

Anil_Babu_Samineni

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
Master
Master
Author

here it ismnth.PNG

Anil_Babu_Samineni

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)
kamalqlik
Partner
Partner

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
Master
Master
Author

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

kamalqlik
Partner
Partner

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.

kamalqlik
Partner
Partner