Date problem

[risabhroy_03]

I have a table called "WHUIL200" and there is a date field present "T$INDT".

I have to filter this table based on the dimension "T$INDT"=last month end date.

Load
Company_Code,
"T$ITEM" & '|' & Company_Code as [Temp_Key_Item],
"T$ITEM" as [Item],
"T$CWAR" as [Warehouse],
"T$QNTY" as [Quantity],
"T$AMNT" as [Value],
Date("T$INDT",'DD-MM-YYYY') as "T$INDT",
'WHUIL200' as [Table Source]

FROM [$(vSource)\TWHUIL200.qvd](qvd);
 where ("T$INDT" = '$(vLastmonthdate)');

but everything comes blank, when I remove the where condition it shows all the data(unfiltered).

This is my variable let vLastmonthdate =date(floor(monthend(addmonths(Today(),-1))));

This variable works perfect for all other tables.

[Or]

I'd suggest running the script step by step using debug mode and seeing what actual comparison is made (that is, what the exact variable value is). You can then cross-check that with the actual value in the table and see if they match or not.

You could also just load this value as a field in your table and then check if that matches up:

Load
Company_Code,
"T$ITEM" & '|' & Company_Code as [Temp_Key_Item],
"T$ITEM" as [Item],
"T$CWAR" as [Warehouse],
"T$QNTY" as [Quantity],
"T$AMNT" as [Value],
Date("T$INDT",'DD-MM-YYYY') as "T$INDT",

'$(vLastmonthdate)' as TestDate,
'WHUIL200' as [Table Source]

FROM [$(vSource)\TWHUIL200.qvd](qvd);

[tresesco · Accepted solution]

Your date format is possibly not matching with the variable value format. You can avoid this by using num() in both date and variable like:

where num(Floor("T$INDT")) = num($(vLastmonthdate));