- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Display value based on two different Variables
Hi,
I have a table below. and my requirement is
Field1 | Field2 |
A | 3/13/2024 |
A | 5/25/2023 |
A | 2/15/2024 |
A | 1/25/2024 |
B | 3/13/2024 |
B | 5/25/2023 |
B | 3/15/2024 |
B | 1/25/2024 |
Selection For "A" in Field 1
Variable 1(VFilterFirstMonthCompare) : JAN 24
Variable 2(VFilterSecondMonthCompare) : APR 24
Output Should be :
if I select Variable 1 as JAN 24 and Variable 2 as APR 24, THEN it should show 1/25/2024 and No Data available for APR 24
Selection For "B" in Field 1
Variable 1(VFilterFirstMonthCompare) : MAR 24
Variable 2(VFilterSecondMonthCompare) : JAN 24
Output Should be :
if I select Variable 1 as MAR 24 and Variable 2 as JAN 24, THEN it should show3/15/2024 and 1/25/2024
I have filter for Field 1 for the Product A
and using Variable input Extension, i select 2 variables for comparision.
And in Straight table, i want to compare the values of Prduct A for those month in a single row.
Output be like: in straight table single row
Field1, First Month, First Month Volume, Second Month, Second Month Volume, Variance
A, FEB 24, 100.5 MAR 24, 99.00 ,-1.5
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello, try this as a measure
only({<Field2={" >=$(= $(VFilterFirstMonthCompare) <=$(= $(VFilterSecondMonthCompare) "}>}Field2)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Regarding your first example: I'm assuming you have a full calendar for the dates. If there is no date in Apr 2024 in the data, Apr 2024 will not be shown in the table.
Regarding your second example: You set Variable 1 to MAR 24 and 3/15/2024 is shown. But you have a second data point in Mar 2024: 3/13/2024. How is it decided what values need to be shown?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have filter for Field 1 for the Product A
and using Variable input Extension, i select 2 variables for comparision.
And in Straight table, i want to compare the values of Prduct A for those month in a single row.
Output be like:
Field1, First Month, First Month Volume, Second Month, Second Month Volume, Variance
A, FEB 24, 100.5 MAR 24, 99.00 ,-1.5
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Tihis is what I managed to create:
Field 1 is the only dimension in the table. All other columns are measures:
First Month:
'$(vFilterFirstMonthCompare)'
First Month Volume:
{<Field2_MonthYear = {'$(vFilterFirstMonthCompare)'}>} If(Count(Value) > 0, Sum(Value))
Second Month:
'$(vFilterSecondMonthCompare)'
Second Month Volume:
{<Field2_MonthYear = {'$(vFilterSecondMonthCompare)'}>} If(Count(Value) > 0, Sum(Value))
Variance:
Column(4) - Column(2)
Both variable inputs use dynamic values for the dropdowns with this expression:
Concat(distinct all Field2_MonthYear, '|', Field2_MonthYear)
As you might have noticed, I've added a new field: Field2_MonthYear. This field is formatted with 'MMM YYYY' and is what is controlled using the variables. Here is the full load script:
Data:
NoConcatenate Load
*,
Date(MonthStart(Field2), 'MMM YYYY') as Field2_MonthYear,
RecNo() as Value;
Load
Field1,
Date(Date#(Field2, 'M/DD/YYYY')) as Field2
Inline [
Field1 Field2
A 3/13/2024
A 5/25/2023
A 2/15/2024
A 1/25/2024
B 3/13/2024
B 5/25/2023
B 3/15/2024
B 1/25/2024
] (delimiter is ' ');
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you shows us output table of you scenerio 2 where B is selected in Field1 and variable 1 value is March. Because there are two different values of march in that case, i just want to know if you are picking the larger value in that case because it seems so.