Jump to content
We've recently updated our Privacy Statement, available here. ×

I have created a Data Function Propery I have a bar chart with Last 4 Quarter results. When I Select 2023 Q4 ( IT IS SHOWING 2023 Q1 Q2 Q3 Q4) Results When I select 2023 ALL - It is not updating All) still showing quarter results


Ahamed Basha
Go to solution Solved by Gaia Paolini,

Recommended Posts

Iron Python Script:

from Spotfire.Dxp.Application.Visuals import *

from Spotfire.Dxp.Data import *

myVis = myVis.As[Visualization]()

if Document.Properties["PQuarter"] == 'All':

myVis.XAxis.Expression = "<[impact_year]>"

myVis.Data.WhereClauseExpression = ''

myVis.Data.WhereClauseExpression = "([impact_year]>=String(Integer('${PYear}')-3) and [impact_year]<String(Integer('${PYear}')+1))" 

else :

myVis.XAxis.Expression = <PruneHierarchy([VW_PUBS01_SAMPLES_QR].[Hierarchy.Year_Quarter_New],1)>

myVis.Data.WhereClauseExpression = ''

myVis.Data.WhereClauseExpression ="(Case When '${PQuarter}'='Q4' then ([impact_year]=('${PYear}') and [Quarter_Hierarchy]<= ('${PQuarter}')) else (Integer([impact_year])=(Integer('${PYear}')-1) and [Quarter_Hierarchy]> ('${PQuarter}')) or (([impact_year])=('${PYear}') and [Quarter_Hierarchy]<= ('${PQuarter}')) end)"

Link to comment
Share on other sites

Your expressions really depend on the setup, e.g. the values of [impact_year], the [Quarter_Hierarchy], the [VW_PUBS01_SAMPLES_QR], what is the document property PYear etc. 

The second expression is also quite complex, so it would really help if you could share a dxp with some anonymized sample data.

Can you also elaborate on the desired result?

You want only Q4 or all quarters up to Q4?

Why do you want to show 2022 records when you select 2023?

Link to comment
Share on other sites

Hi Gaia,

Thanks for answering the Question.

My Requirement:

The Report should show

Ex:

When I select 2023 Q4 it should show 2023 Q1,Q2,Q3,Q4( When I select Single Quarter it should show last/previous 4 quarters)

Same as When i select Quarter = all it should show last 4 years summary data

Hope it got clarified

Link to comment
Share on other sites

@Gaia Paolini​ 

Please check the screenshot.

So When I select 2023 Q4 - I have to get 2023 Q1 Q2 Q3 Q4 Values.(Pyear = 2023 ) and PQuarter = 'Q4'

Same when I select 2023 Q3 - I have to get 2022 Q4 2023 Q1 Q2 Q3 Values((Pyear = 2023 ) and (PQuarter = 'Q3')

when I select Pquarter = 'All' then I have to get 2022 values and 2023 Values (Pyear = 2023) and (Pquarter = 'All')

Hope This Clarifies Gaia.

Link to comment
Share on other sites

Iron Python Script:

from Spotfire.Dxp.Application.Visuals import *

from Spotfire.Dxp.Data import *

myVis3 = myVis3.As[Visualization]()

if Document.Properties["PQuarter"] == 'All':

myVis3.XAxis.Expression = "<[impact_year]>"

myVis3.Data.WhereClauseExpression = ''

myVis3.Data.WhereClauseExpression = "([impact_year]>=String(Integer('${PYear}')-3) and [impact_year]<String(Integer('${PYear}')+1))" 

else :

myVis3.XAxis.Expression = "<PruneHierarchy([VW_PUBS01_SAMPLES_QR].[Hierarchy.Year_Quarter_New],1)>"

myVis3.Data.WhereClauseExpression = ''

myVis3.Data.WhereClauseExpression ="(Case When '${PQuarter}'='Q4' then ([impact_year]=('${PYear}') and [Quarter]<= ('${PQuarter}')) else (Integer([impact_year])=(Integer('${PYear}')-1) and [Quarter]> ('${PQuarter}')) or (([impact_year])=('${PYear}') and [Quarter]<= ('${PQuarter}')) end)"

Link to comment
Share on other sites

What I could understand without an example is this:

if PQUARTER=All then 

  X=year 

expression = year>=String(PYEAR-3) and year<String(PYEAR+1)    

else:

  X = I don't know??

expression = 

      if PQUARTER=Q4 then year=PYEAR and quarter<=Q4

      ELSE

        year = PYEAR-1 and quarter>PQUARTER

        or 

       year = PYEAR and quarter<=PQUARTER

  

  which translates to:

  PQUARTER=Q4        >> then PYEAR and quarter=Q1,Q2,Q3,Q4

  PQUARTER=Q1 and PYEAR=2022 >> then 2021 quarter=Q2,Q3,Q4 or 2022 and quarter=Q1

  PQUARTER=Q2 and PYEAR=2022 >> then 2021 quarter=Q3,Q4 or 2022 and quarter=Q1,Q2

  PQUARTER=Q3 and PYEAR=2022 >> then 2021 quarter=Q4 or 2022 and quarter=Q1,Q2,Q3

  PQUARTER=Q1 and PYEAR=2023 >> then 2022 quarter=Q2,Q3,Q4 or 2023 and quarter=Q1

  PQUARTER=Q2 and PYEAR=2023 >> then 2022 quarter=Q3,Q4 or 2023 and quarter=Q1,Q2

  PQUARTER=Q3 and PYEAR=2023 >> then 2022 quarter=Q4 or 2023 and quarter=Q1,Q2,Q3

You say the issue is this:

When I select 2023 ALL - It is only showing 2022 Q4 it has to show 2022,2023 Year records

The corresponding expression translates to this:

year >=String(2020) and year <String(2024)

which would seem ok but I don't know what the String(..) is doing as you are comparing numbers not strings.

Link to comment
Share on other sites

Please modify Query:

from Spotfire.Dxp.Application.Visuals import *

from Spotfire.Dxp.Data import *

myVis2 = myVis2.As[Visualization]()

if Document.Properties["PQuarter"] == 'All':

myVis2.XAxis.Expression = "<[impact_year]>"

myVis2.Data.WhereClauseExpression = ''

myVis2.Data.WhereClauseExpression = "([impact_year]>=String(Integer('${PYear}')-3) and [impact_year]<String(Integer('${PYear}')+1))" 

else :

myVis2.XAxis.Expression = "<PruneHierarchy([VW_PUBS01_SAMPLES_QR].[Hierarchy.Year_Quarter_New],1)>"

myVis2.Data.WhereClauseExpression = ''

myVis2.Data.WhereClauseExpression ="(Case When '${PQuarter}'='Q4' then ([impact_year]=('${PYear}') and [Quarter_Hierarchy]<= ('${PQuarter}')) else (Integer([impact_year])=(Integer('${PYear}')-1) and [Quarter_Hierarchy]> ('${PQuarter}')) or (([impact_year])=('${PYear}') and [Quarter_Hierarchy]<= ('${PQuarter}')) end)"

Link to comment
Share on other sites

I think in these cases it is a really helpful tool to break down the logic into smaller bits and check each result in turn.

I have modified the script as below (please use the script insertion with symbol </> so formatting is kept).

You will have to rename the columns within.

I simplified the if statements as you were inserting a lot of logic into the case for quarter not equal to Q4 into the whereClauseExpression, whereas now it is in the script.

 

from Spotfire.Dxp.Application.Visuals import *
from Spotfire.Dxp.Data import *
 
 
myVis2 = myVis2.As[Visualization]()
PQuarter = Document.Properties["PQuarter"] 
PYear = Document.Properties["PYear"] 
 
if PQuarter == 'All':
 
	myVis2.XAxis.Expression = "<[Year]>"
	myVis2.Data.WhereClauseExpression = "[Year]>=Integer('${PYear}')-3 and [Year]<Integer('${PYear}')+1" 
 
else :
	myVis2.XAxis.Expression = "<PruneHierarchy([Hierarchy.Year_Quarter_New],1)>"
	if PQuarter == 'Q4':
		#there is no Q5 so Quarter<=PQuarter is not needed
		myVis2.Data.WhereClauseExpression =" [Year]=Integer('${PYear}') "
	else:
		myVis2.Data.WhereClauseExpression ="([Year]=(Integer('${PYear}')-1) and [Quarter]> '${PQuarter}') or (  [Year]=Integer('${PYear}') and [Quarter]<= '${PQuarter}'  )"

For completeness of testing I also added the missing quarters to the data for year 2022.

This is what I am getting. For simplicity I use PQ=PQuarter and PY=PYear and Y=Year and Q=Quarter:

You expect:

When I select Pyear = 2023 & Pquarter = 'All' it is showing only 2022 Q4 results.

it has to show 2022 & 2023 Year summary results.

This seems to be ok now.

when PY is equal to All you get:

Y> PY-3 and Y< PY+1

 when PY=2022 you get

  2022> 2022-3 true

2022 < 2022+1 true

  2023> 2022-3 true

2023 < 2022+1 false

 

 all quarters for 2022 only

  

 when PY=2023 you get all quarters for 2022 and 2023

 

when PY is not equal to All, and Q4 you get:

all the quarters for the year PY

when PY is not equal to All, and Q1,Q2,Q3 you get:

first clause:       second clause:

Y=PY-1 and Q>PQ  or   Y=PY and Q<=PQ

PQ=Q1 and Year=2022:

 nothing from the first clause as we only go back to 2022

 Y=2022 and Q=Q1

  

PQ=Q1 and Year=2023:

 Y=2022 ans Q=Q2,Q3,Q4

 Y=2023 and Q=Q1

PQ=Q2 and Year=2022:

 nothing from the first clause as we only go back to 2022

 Y=2022 and Q=Q1,Q2

  

PQ=Q2 and Year=2023:

 Y=2022 and Q=Q3,Q4

 Y=2023 and Q=Q1,Q2

PQ=Q3 and Year=2022:

 nothing from the first clause as we only go back to 2022

 Y=2022 and Q=Q1,Q2,Q3

  

PQ=Q3 and Year=2023:

 Y=2022 and Q=Q4

 Y=2023 and Q=Q1,Q2,Q3

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...