topic Re: How to convert YTD data to Month data in QlikView

How to convert YTD data to Month data

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

Dear All,

i need help to find the solution for changing my YTD data to Month data. I am looking for an expression for single month data, but i have only YTD months. ( it means Month 1 = January data, Month 2 = January +February data)

An example of my data

I will be glad for any answer

Robert

Re: How to convert YTD data to Month data

trdandamudi — Sat, 13 Aug 2016 18:54:49 GMT

The way I understood is, you want to convert the MonthYTD into specific month. Example 1 as January, 2 as February and so on.. if that is the case then please see below:

Map_Month:

Mapping Load * Inline [

Month,MonthName

1, January

2, February

3, March

4, April

5, May

6, June

7, July

8, August

9, September

10, October

11, November

12, December

];

Data:

LOAD Year,

ApplyMap('Map_Month',[Month YTD]) as MonthYTD,

Product_ID,

Customer_ID,

[Gross Sales],

[Ne Sales],

[profit Margin]

FROM

(ooxml, embedded labels, table is [PM 2015 FY YTD]);

Re: How to convert YTD data to Month data

vinieme12 — Sun, 14 Aug 2016 07:01:05 GMT

Hi Robert,

Can you specify a dataset within the example provided , I checked with a combination of ProdID+CustomerID and the data doesn't look like it is YTD.

Are the figures accumulated by Product only ? or Customer only? I find this highly unlikely

Re: How to convert YTD data to Month data

kaushiknsolanki — Sun, 14 Aug 2016 10:13:25 GMT

Hi,

Here is the app.

Regards,

Kaushik Solanki

Re: How to convert YTD data to Month data

Anonymous — Sun, 14 Aug 2016 14:32:08 GMT

Hi Vineeth

the data was only an example ( probalbly without the same Customer ID or/and Product ID) but in should be for sure YTD. I need only a expression for month data ( for Customer ID, Product ID ect)

In a mindtime i found this expression, that seems to work:

Sum({$< Year = {$(=max(Year))}, Month = {$(=Max(Month))}>} [Net Sales])-Sum({$< Year = {$(=max(Year))}, Month = {$(=Max(Month)-1)}>} Net Sales)

Re: How to convert YTD data to Month data

Anonymous — Sun, 14 Aug 2016 14:36:28 GMT

Dear Kaushik,

i can not open this file because i have QW 12 personal edition and Qlik Sense Desktop 3.0