Display value based on two different Variables

[rammuthiah]

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

[Clement15]

Hello, try this as a measure

only({<Field2={" >=$(= $(VFilterFirstMonthCompare) <=$(= $(VFilterSecondMonthCompare) "}>}Field2)

[LRuCelver]

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?

[rammuthiah]

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

[LRuCelver]

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 '	');

[Gabbar]

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.