topic Re: previous month in QlikView

previous month

raadwiptec — Wed, 25 Nov 2020 16:16:04 GMT

I have 2 source files

source file 1

Month ¦Year¦ Invoiceno¦ Quantity¦ Country

jan 2016 1000001

feb

mar

dec

Source File 2 - Here iam taking year and month from invoice date ..ex Year(Invoice Date) as Year...and Month(Invoice Date) as Month.

Invoiceno ¦Quantity ¦Invoice Date ¦ Year ¦ Month

1000001 123 22/6/2016 2016 01

My output - All my data is good..But for Quantity start of Month I need the previous month (Iam not sure because source 1 as jan,feb etc and source 2 has 01,02 etc...may be it the problem.. I get the output as zero...)

============

Country¦ Invoice¦ Quantity Start¦ Quantity EOM

Re: previous month

Anonymous — Mon, 05 Sep 2016 07:55:45 GMT

Hi,

How do you know which is the start month quantity?, If you know this quantity, you can add one month to your date with AddMonths, and calculate then the month and year.

Regards!

Re: previous month

raadwiptec — Mon, 05 Sep 2016 08:07:45 GMT

Quantity is from merge of first and second source file.. but in the spource file 1 ..month is like jan,feb..and source 2 month is 01,02..

Re: previous month

rajuamet — Mon, 05 Sep 2016 08:09:53 GMT

Hi raadwiptec,

Can u provide us a sample application. Output depends on how u have linked source 1 and 2.

Re: previous month

Peter_Cammaert — Mon, 05 Sep 2016 08:11:02 GMT

What is the difference between source file 1 and 2? Does one contain Stock levels and the other Consumptions?

Re: previous month

Peter_Cammaert — Mon, 05 Sep 2016 08:12:07 GMT

Just convert those MM-YYYY and MMM-YYYY values to a common date value (using the date#() or MakeDate() functions) and you'll be on your way.

Re: previous month

Peter_Cammaert — Mon, 05 Sep 2016 08:15:33 GMT

For example, you can use Date#('1-' & Month & '-' & Year, 'D-MMM-YYYY') to create a new date value while loading the 1st file, and MakeDate(Year, Month, 1) to create a date column from the 2nd source file.

Re: previous month

Anonymous — Mon, 05 Sep 2016 08:24:55 GMT

If you want to add month like Jan, Feb in source table file 2 then before merging with source table file 1, map a inline load in soure table file 2 like this?

Mapping

Load * inline

[MonthNo, Month

1,Jan

2,Feb

3,Mar

so on..

];

Replace/Alias Month in Source File 2 as MonthNo and then use applymap() to map those value in source file 2.

Hope this will help!

Re: previous month

Anonymous — Mon, 05 Sep 2016 08:32:56 GMT

Hi,

translate it

Sourcefile1

MonthName(Date(Date#(Month&'-'&Year,'MMM-YYYY'))) as MonthName

Sourcefile2

Monthname([Invoice Date]) as MonthName

Regards!!

Re: previous month

raadwiptec — Mon, 05 Sep 2016 09:32:35 GMT

hi all,

is there a way to add months for MMM formats?

Re: previous month

Anonymous — Mon, 05 Sep 2016 09:51:18 GMT

Hi,

Addmonths(Date(Date#(Month&'-'&Year,'MMM-YYYY')),NumOfMOnthsToAdd)

Regards!

Re: previous month

raadwiptec — Mon, 05 Sep 2016 10:04:18 GMT

iam trying like this but soesnt work

Sum({<[Month]={Addmonths(Date(Date#(Month&'-'&Year,'MMM-YYYY')),1))}>}[Quantity])

Re: previous month

Anonymous — Mon, 05 Sep 2016 10:10:14 GMT

Hi,

Could you attach one example of your data and expected output please?

It will be more helpfull.

Regards!!