Issue after concatenating

[Anonymous]

Dear All

I have two tables  Table1- Sales and Table2- Outstanding.   I have concatenated both tables.

Both have Date field of their own.

The Sales table field--->  YearMonth  is linked to calendar.  I am given alias YearMonth to date field of Outstanding.

I am trying to create a report which has to show both Sales and Outstanding amounts for the previous month.

For which I use variable in Set analysis as  vPrevmonth.

Variable is defined as below:

vPrevmonth = Date(addmonths(max([MonthYear]), -1), 'MMM-YYYY')  .

However I do not get the desired result for Sales and Outstanding. The output works for only one field either Sales or Outstanding.

Not for both.

Is there any modification required?  Kindly help.

Thanks & Regards

Chintan Gala

[Anonymous]

Thanks Sunny, I will try and let u know!

Regards

Chintan

[Anonymous]

Hello Sunny

It still shows 0 for Sales. Also a small correction.

In Outstanding table the date is as      date([Month Year],'MMM-YYYY') as OS_MY,

and not MonthYear. while reloading I understood.

Regards

Chintan

[sunny_talwar]

My bad, I didn't realize that there was another tab.... Updated it now, can you check

[Anonymous]

Hello Sunny

Also I have put

Date(MonthStart(MonthYear),'MMM-YYYY') as OS_MY,

for all dates in  the Fact, Outstanding and Calender.

Note:  The date in Fact and Outstanding are different which I am trying to alias with same name to use in variable. Is that causing issue?  is it correct?

Thanks & Regards

Chintan

[sunny_talwar]

Can you reload the attached file and repost it after you have reloaded this?

[Anonymous]

while for the Outstanding date as

Date(MonthStart([Month Year] ),'MMM-YYYY') as OS_MY,

[Anonymous]

Dear Sunny

Kindly find attached.

Note: I made one change to date in Outstanding table as [Month Year]  instead of [MonthYear]

Regards

Chintan

[sunny_talwar · Accepted solution]

I think QlikView is not able to interpret your MonthYear field within the Fact table. You will need to use Date#() to help qlikview understand your MonthYear field and then you can use your own formatting. Lets say MonthYear looks like this

MM/YYYY

Date(MonthStart(Date#([MonthYear]), 'MM/YYYY')), 'MMM-YYYY') as OS_MY,

Now I don't have source data file, so don't really know how MonthYear looks like, but if you can reload the application once again with MonthYear field in your Fact table, I might be able to see how it looks like.

Also, read these

Why don’t my dates work?

QlikView Date fields‌

Get the Dates Right

[Anonymous]

Hello Sunny

I will check and let you know.

Thanks & Regards

Chintan Gala

[Anonymous]

Hello Sunny

Thanks a lot for your explanation.

Am still to check, just wanted one clarification. I am trying to figure out the format of dates in all tables from extractors.

If the field MonthYear comes in same format in all three tables --  1) Fact ,2) Calendar and 3) Outstanding then that variable should work and show me Sales and Outstanding figures in the same report?  (currently it shows Sales 0 as the date format  in Fact table is  in different format).

Is my above understanding from you correct? 

Thanks & Regards

Chintan Gala