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]

I have created a Data Function Propery for updation of Quarters.

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 only showing 2022 Q4 it has to show 2022,2023 Year records

[Ahamed Basha]

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

[Gaia Paolini]

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?

[Ahamed Basha]

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

[Ahamed Basha]

[Ahamed Basha]

@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.

[Ahamed Basha]

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

[Gaia Paolini]

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.

[Ahamed Basha]

Correct Gaia.

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

it has to show 2022 & 2023 Year summary results.

Data Available in Data Table

[Ahamed Basha]

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

[Gaia Paolini]

I understand what you are expecting, but unfortunately without an example dxp I cannot help you further. I am unable to work it out from screenshots.

[Gaia Paolini]

Hi, unfortunately I can only see an empty dxp and no guidelines.

Please remember not to share confidential information as this is a public community. Please review the guidelines for asking questions so that the issue can be scoped and understood:

https://community.spotfire.com/s/article/Quick-Tips-on-Asking-Community-Questions

[Ahamed Basha]

Sorry Gaia.

I have created a test data and test dxp file.

[Gaia Paolini]

I can open file and data, thanks, but there is no scipt. When I add the script you shared, I am missing two properties: [impact_Year] and [Quarter_Hierarchy].

Guessing [impact_Year] is [Year] and I am using [Quarter] for [Quarter_Hierarchy].

[Gaia Paolini]

However I am still missing <PruneHierarchy([VW_PUBS01_SAMPLES_QR].[Hierarchy.Year_Quarter_New],1)>

[Gaia Paolini]

I have created the hierarchy tentatively using Year and Quarter.

[Gaia Paolini]

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

[Ahamed Basha]

Can you please attach .dxp file if you have handy

[Gaia Paolini]

This was created on Spotfire 12.5