Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Count number of months

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.

10 Replies
Not applicable

Re: Count number of months

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

StoreOpenSince.PNG

MVP
MVP

Re: Count number of months

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


Employee
Employee

Re: Count number of months

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? 

Not applicable

Re: Count number of months

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..

Not applicable

Re: Count number of months

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

   

SALE DATEMONTHOPERATION MONTH
20150212021
20150212021
20150212021
20150213021
20150213021
20150219021
20150316032
20150327032
20150430043
20150522053
Not applicable

Re: Count number of months

Sorry I made a mistake..

   

SALE DATEMONTHOPERATION MONTH
20150212021
20150212021
20150212021
20150213021
20150213021
20150219021
20150316032
20150327032
20150430043
20150522054
Employee
Employee

Re: Count number of months

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>

Not applicable

Re: Count number of months

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
Contributor III

Re: Count number of months

Hi,

attached a sample .qvw.

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

Regards,

Patric