Count number of months

[#1]

Hi,  I'm new in Qlikview and what I need to do is count numbers of month of operation for the different stores... How do I do that?... For example if a store started sale since July 2015 for July I must to count 1 and 2 for August... I want to do this creating a new dimension in Script Editor...

Please help.

[#2]

There are two options here. You can use the script editor OR you can use SQL to add this field while loading the data. I prefer SQL so I am going to share that:

(here's an example of SQL against MS SQL Server Database but you can adjust this based on your target database)

select StoreOpenDate, DATEDIFF(mm,StoreOPenDate,GETDATE()) as OpenSinceNumOfMonths from StoreDimension

[jonathandienst]

Assuming you don't have the opening date, but you can get it from the first transaction date, you could do something like this:

Join (Sales)

LOAD Store,

  Min(Date) As OpenDate

  Month(Min(Date)) * 12 + Year(Min(Date)) As OpenMonthSequence,

Resident Sales

Group By Store;

Now in the front end, in a table/chart with Store as a dimension use an expression like this for months of operation

  =(Month(Today()) * 12 + Year(Today())) - OpenMonthSequence

[JonnyPoole]

i can think of a couple ways to do this.

You can do a resident load in the data load editor that loads all stores and does a month(min(OrderDate)) and year(min(OrderDate)) from the orders table.  That way you have the first month for each store in the data model and it will be relatively easy to get a count of months between that date and today's date.

If not, i think its still possible with a more involved set analysis statement, but that depends on your data model structure. Anyway you can send sample data / model to build a sample? 

[#5]

I did this:

   Select [FECHA FACTURA], DATEDIFF(mm,[FECHA FACTURA],GETDATE()) as OpenSinceNumOfMonths

FROM
[..\..\..\BASE VTAS PUNTOS DE VENTA.xlsx]
(ooxml, embedded labels)

But when I try to run the script don't work..

[#6]

Jonathan what I want to do is to créate a new field "Month Operation" in the spript:

SALE DATE	MONTH	OPERATION MONTH
20150212	02	1
20150212	02	1
20150212	02	1
20150213	02	1
20150213	02	1
20150219	02	1
20150316	03	2
20150327	03	2
20150430	04	3
20150522	05	3

[#7]

Sorry I made a mistake..

SALE DATE	MONTH	OPERATION MONTH
20150212	02	1
20150212	02	1
20150212	02	1
20150213	02	1
20150213	02	1
20150219	02	1
20150316	03	2
20150327	03	2
20150430	04	3
20150522	05	4

[JonnyPoole]

assuming all the data is from the same year, you can determine the minmonth in the data first, then when you load the actual data do a subtraction.  A level of complexity is added if you need to do different monthstarts for different stores and if your data spans different years. Let us know if either is the case.

MinMonthTable:

load min(Month) as MinMonthField;

from <source>;

let vMinMonth=peek('MinMonthTable',0,'MinMonthField');

Load

     [Sale Date],

     Month

     Month-$(vMinMonth)+1 as [Operation Month];

from <source>

[#9]

Yes each store opened in different month and they have sales since 2013 in some cases.

I will work with a data base which is updating all days and I need to calculate the "month operation number" for each sale.

[pamaxeed]

Hi,

attached a sample .qvw.

Try to give a look and check if it works for you.

Regards,

Patric