Fiscal Year & Previous Fiscal Year for Pivot Chart

[#1]

Hi,

I'm using a Multi-box selection for Fiscal Year (format 2012-2013, 2013-2014 etc.) where I'm generating a Pivot Chart Sales Data. Now, when I'm Selecting one FYear from Multi-box, accordingly Pivot Chart Sales Data appears FYear wise & Quarter wise since both FYear and Quarter are in the Dimension list. Again, when I'm Selecting Several FYears from Multi-box, accordingly several Fiscal Years' Sales Data appear by Quarter side-by-side.

Now, I want to change this in a way that when I will select One FYear from the Multi-box, only Selected Year's Quarter wise Sales Data and it's Previous Year's Quarter wise Sales Data will appear. Also note that no multiple selection will not be allowed in the FYear Multi-box.

How this can be achieved? Thanks in Advance.

- Sajjad.

[Anonymous]

It can be done using calculated dimension for fiscal year using set analysis.  How exactly - it depends on the specific of your Fiscal Year format.  Is it just a text or a dual?  It is simpler with dual.

Hope it gives you the direction.  If it's not enough - upload a small example of your application.

[#3]

Hello Michael,

Sorry for the late response.... I suppose my FYear is a Dual format. I've uploaded a small example of my application herewith for your ready reference and suggestion.

Thanks,

- Sajjad.

[Anonymous]

See attached.  Notice set analysis in calculated dimension for Fiscal Year, and same set in expression.

[#5]

Hi,

You can get your desired output by changing your original chart expression from

Sum(BOIQOD)

to

Sum({<fYear={">=$(=Max(fYear)-1)<=$(=Max(fYear))"},FYear=>}BOIQOD)

Chart dimensions will be same as in your original chart

Regards

Sana
 

[Anonymous]

Agree, my habit to use calculated dimension is not the most effective in this case.  Expression with set is enough here.

[#7]

Thanks Sana! Thanks a Ton!

- Sajjad.

[#8]

Hello Michael,

I've updated the image of my Pivot Chart attached herewith for your ready reference.

Now, I'm finding difficulties when I've attempted to use Pick() for the same expression where Current vs Previous Year Sales comparison should be displayed for the TopN customers of currently selected FY. [The chart lay-out should be as per the chart# 1.]

In Chart# 1, I've put the expression for TopN customers sales of currently selected FY with previous year comparison. Here, 'Sub-Total', 'Others' & 'Grand Total' expression had not set.

Expression in Chart# 1:

Sum({

       <FYear+={"$(=(max(FYear)-2) & '-' & (max(FYear) -1))"} ,

       CustomerName={'=Rank(SUM([BOIQOD]) / [TK_Amount])<= [vTopX]'}> }    

       [BOIQOD]/ [TK_Amount] )

In Chart# 2, soon I've just added Pick() for the same expression used in Chart# 1, it got messed up.

Expression in Chart# 2:

= Pick(Measure,

  Sum({

       <FYear+={"$(=(max(FYear)-2) & '-' & (max(FYear) -1))"} ,

       CustomerName={'=Rank(SUM([BOIQOD]) / [TK_Amount])<= [vTopX]'}> }    

       [BOIQOD]/ [TK_Amount] )

)

In Chart# 3, full expression for Pick() been added but result almost same.

Expression in Chart# 3:

= Pick(Measure,

  Sum({

       <FYear+={"$(=(max(FYear)-2) & '-' & (max(FYear) -1))"} ,

       CustomerName={'=Rank(SUM([BOIQOD]) / [TK_Amount])<= [vTopX]'}> }    

       [BOIQOD]/ [TK_Amount] ),

  Sum({

       <FYear+={"$(=(max(FYear)-2) & '-' & (max(FYear) -1))"} ,

       CustomerName={'=Rank(SUM([BOIQOD]) / [TK_Amount])<= [vTopX]'}> }    

       [BOIQOD]/ [TK_Amount] ),

  Sum({

       <FYear+={"$(=(max(FYear)-2) & '-' & (max(FYear) -1))"}> }    

       ([BOIQOD]/ [TK_Amount]) )

-

  Sum({

       <FYear+={"$(=(max(FYear)-2) & '-' & (max(FYear) -1))"} ,

       CustomerName={'=Rank(SUM([BOIQOD]) / [TK_Amount])<= [vTopX]'}> }    

       [BOIQOD]/ [TK_Amount] ),

  Sum({

       <FYear+={"$(=(max(FYear)-2) & '-' & (max(FYear) -1))"}> }    

       ([BOIQOD]/ [TK_Amount]) )

)

Would appreciate if you please look into and provide solution how I can overcome the problem for Chart# 3 and get correct set of Data as per lay-out Chart# 1.

Thanks in Advance.

- Sajjad.

[Anonymous]

Sajjad,

First, it doesn't look to me as a working expression.

Second, this follow-up question is not related to your opening topic.  I'd recommend to open a separate discussion for this (most people don't look at the open discussions with many replies), and attach your new qvw file.

Regards,

Michael

[#10]

Well Michael, I posted the qvw as a new question as suggested.

Thanks,

- Sajjad.