I have a date field (date type) a period field (string type) and value field(int type). my period is change according to my date. Example of my data
Date Period Value
2021-12-25 13 5
2022-01-01 13 2
2022-01-07 01 1
2022-01-14 01 3
So every week has a period, the max of the period is 13.
In my control area, i add a date parameter that i connected with my visuals with my field date.
I want to create a table that will group these periods according to my $Date (parameter)
For example i select in my filters the day 2022-01-01
Table visual:
Period Value
13 7
If i will change the filter on the top to 2022-01-14
Table visual:
Period Value
01 4
More or less i want to create a table, present the period and values and the period will change according to my $Date parameter from my control
I tried to create this calculation to truck the current period⌠but it is not working(I know why is not working but i am presenting the calculation in case it is needing)
Current Period
ifelse(extract(âDDâ,${Date})=extract(âDDâ, {date}), {period},0)
Current Period
ifelse( extract(âDDâ, ${pDate})=extract(âDDâ, Date), 1 ,0)
1 Like
Thank you Rob for your response
But if you select the 2022-01-14 you will not be able to see the values from the 2022-01-07. again you will see
Period Value
01 3
While i need to see
Period Value
01 4
Hello @Sofia-Bourika - Thank you for posting your query. I have a crude idea in my mind, which may work in your case. I am trying to depict my solution below for your reference.
Step 1 : Create a Calculated Field called âPeriod Measureâ. This is to convert the Period (which is originally string) into an integer datatype.
Step 2 : Create a Parameter called paramDate and add that parameter control into sheet for easy visibility.
Step 3 : Create another Calculated Field called âSelected Periodâ to utilize it for flagging the qualifying records as 1 and non-qualifying records as 0.
The expression is given below for your additional reference
ifelse(maxover(ifelse(Date = ${pramDate}, {Period Measure}, 0), , PRE_AGG) = {Period Measure}, 1, 0)
Step 4 : Create a Filter using that âSelected Periodâ calculated field to only include the value 1.
Step 5 : Hide the irrelevant columns in the main Table visual to represent according to your need.
Let me know if this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as âSolutionâ. This will help the community to find guidance and answers to similar question. Thank you!
2 Likes
Thank you soooo much @sagmukhe
I am sooo close of the solution of my issue.
I have an other question for you if you can help me,
the period is change every week but we donât have data for every day example:
The grey cells donât exist in our dataset but our readers would select from the filter one day that doesnât have values (because they donât know which day there are values)
So how can we group again the period when our users select one period without data because from the calculation would be Date <>${pramDate} thus it will present 0
So for example when i am selecting from Control area pramDate=03/01/2022 the result would be
Again much appreciated for your help I am quite close to achieve what i need
@sagmukhe To help you a little bit more I have 2 tables TABLE A:( with my dates and values) and TABLE B (with dates and period), thus i made a left join with a connection of dates
@Sofia-Bourika - You need to take help of a typical Date Dimension Table to ensure that you have all the dates present in order to make this work. You need to have a logic like Date Dimension Left Outer join Table A (having date and Period) Left Outer Join Table B (having date and Value) using the date column. That should solve the problem for you is what I believe. Hope this helps!
Thank you @sagmukhe, I was thinking the same. I just wanted to check if exist something else
Thank you for your help 
again have a lovely day
1 Like
@Sofia-Bourika - I am glad that my suggestions helped you. You too have a wonderful day!! Collaboration is powerful!!
1 Like
