Converted date to Month year doesn't work

[vsap2000]

Hello Guys,

I have developed this set expression in front end of Qlik Sense

for an Invoiced Mth and Booked Mth as shown below,

Invoiced Mth = MonthName(If (Rec_Type = 'I', Book_or_Inv Date, Actual Date))

Booked Mth = MonthName(If (Rec_Type = 'B', Book_or_Inv Date, Actual Date))

I get in a table,

• Actual Date  Book_or_Inv Date Rec Type Invoiced Mth  Booked Mth
• 4/1/2020         9/20/2018                 D               Sep 2018           Apr 2020
• 4/4/2020         9/27/2018                 D                 Sep 2018         Apr 2020
• 4/15/2020       1/29/2019                A                  Apr 2020         Jan 2019
• 5/1/2020         10/20/2018              D                Oct 2018           May 2020
• 5/15/2020       2/18/2020                A                  May 2020         Feb 2020

Issue is, for eg. suppose I filter on Sep 2018 from Invoiced Mth, it doesn't get filtered on that month (here I should get first 2 lines) but picks few other months also, same issue with Booked Mth if I pick Feb 2020 (technically it should give me last line) but it picks other lines also.

Can you please help me out how can I overcome this issue?

Thanks in advance.

- B

[sidhiq91]

@vsap2000  Please use the below code in the script:

NoConcatenate
Temp:
Load Date([Actual Date],'MM/DD/YYYY') as [Actual Date],
Date([Book_or_Inv],'MM/DD/YYYY') as [Book_or_Inv],
[Rec Type]
Inline [
Actual Date,Book_or_Inv,Rec Type
4/1/2020, 9/20/2018, I
4/4/2020, 9/27/2018, Ia
4/15/2020, 1/29/2019, A
5/1/2020, 10/20/2018, D
5/15/2020, 2/18/2020, B
];

NoConcatenate
Temp1:
Load *,
if([Rec Type]='I',Monthname([Book_or_Inv]),Monthname([Actual Date])) as [Invoice Month],
if([Rec Type]='B',Monthname([Book_or_Inv]),Monthname([Actual Date])) as [Booked Month]

Resident Temp;

Drop table Temp;

Exit Script;

This should resolve your issue.

[vsap2000]

Thanks for the reply with solution. Sorry for the confusion, in my Rec Type column instead of D it should be I and instead of A it should be B. 

Can this be achieved in front end visualization. As I don't have access to qvd files. 

Thanks!

Thanks.

[MarcoWedel]

How do you filter on Sep 2018 from Invoiced Mth, with Invoiced Mth being defined as table expression ?
Can you please share screenshots of how you are filtering on this value?

thanks

Marco

[vsap2000]

Sorry for the confusion, I think I was not clear in my question. It is not table expression, I was showing some examples from the table which holds more than 100,000 records. There are more than 1000 records which has Invoiced Mth =  Sep 2018  and similarly for Order Mth there are many records which has Feb 2020. So when I click on Sep 2018 to see how many Invoices were there in that month it doesn't work it brings other months also along with Sep 2018. Hope that clarifies my issue.

Thanks!

[MarcoWedel]

It's still not clear for me where you apply your expression

Invoiced Mth = MonthName(If (Rec_Type = 'I', Book_or_Inv Date, Actual Date))

Maybe you can post a small sample application that demonstrates this issue?

thanks

Marco