topic Re: Cum count error in App Development

Cum count error

Anonymous — Thu, 01 Jun 2017 01:18:22 GMT

I have this situation, I have a qvd in which each month has the accumulated records of the previous month, so I would only have to count them, for example for 2016 the value to show is from Dec 2016, for Q1 is from March 2016 , Q2 is June 2016, this I should see it in a pivot table but I could not do it, and tried to use the formula Count({Periodo = {'$(=MonthName(Max(Periodo))'}Recurso) but this is only me Shows the data of the last period of all data, and when using

Count({<Periodo={'=Monthname(Max(Periodo))'}>}Periodo) shows the calculation for the whole year and not a particular period, my goal is to show for example for the year 2016 the count of December 2016, for Q2 of 2016 the count of June 2016, thanks to who can help me

Re: Cum count error

Vasiliy_Beshentsev — Thu, 01 Jun 2017 09:37:49 GMT

Hello, again.

Maybe it's will be ok for you to use pivot table like this?

Re: Cum count error

Anonymous — Thu, 01 Jun 2017 13:25:20 GMT

Hi Vasily

I need to achieve this result

Re: Cum count error

Vasiliy_Beshentsev — Thu, 01 Jun 2017 13:32:57 GMT

Log:

LOAD

AutoNumber(Id_Recurso) as Id_Recurso,

Log_Periodo as Periodo,

Year(Log_Periodo) as Año,

Month(Log_Periodo) as Mes,

Ceil(Num(Month(Log_Periodo))/3) as Quarter

FROM [lib://Acumulado/Log_Acumulado.qvd]

(qvd);

left join

load

Id_Recurso

,Max(Month(Log_Periodo)) as MaxMonth

Resident Log

group by Año;

Re: Cum count error

Anonymous — Thu, 01 Jun 2017 13:56:23 GMT

I try this way

Log:

LOAD

AutoNumber(Id_Recurso) as Id_Recurso,

MonthName(Log_Periodo) as Periodo,

Num(Log_Periodo) as #Periodo,

Year(Log_Periodo) as Año,

Month(Log_Periodo) as Mes,

Ceil(Num(Month(Log_Periodo))/3) as Quarter

FROM [lib://Acumulado/Log_Acumulado.qvd]

(qvd);

Left Join(Log)

Load

Max(Periodo) as Periodo,

1 as BanderaAño

Resident Log

Group by Año;

Left Join(Log)

Load

Max(Periodo) as Periodo,

1 as BanderaQuarter

Resident Log

Group by Año,Quarter;

I can get the last month of the year and the quarter, but I would not know how to show both,

Re: Cum count error

Vasiliy_Beshentsev — Thu, 01 Jun 2017 14:01:04 GMT

Add max(quarter)

Re: Cum count error

Vasiliy_Beshentsev — Thu, 01 Jun 2017 14:04:52 GMT

Well you try to agregate counts into years but u want to do it not by the years, but by max(Month).

It looks like you need to do synthetic dimension with text of years: 2007, 2008, 2009, and calculate there count by max(Month/Quarter).

Read about ValueList and how it works. It helps me sometimes in not standart situations.

Re: Cum count error

Anonymous — Thu, 01 Jun 2017 14:07:02 GMT

I understand, thank you so much for your time Vasily, I'll read about it.

Re: Cum count error

Anonymous — Thu, 01 Jun 2017 15:03:35 GMT

Arrive to the desired result, thanks to the tips of vasiliy_beshentsev, this was my solution

Code:

Log:

LOAD

AutoNumber(Id_Recurso) as Id_Recurso,

MonthName(Log_Periodo) as Periodo,

Num(Log_Periodo) as #Periodo,

Year(Log_Periodo) as Año,

Month(Log_Periodo) as Mes,

Ceil(Num(Month(Log_Periodo))/3) as Quarter

FROM [lib://Acumulado/Log_Acumulado.qvd]

(qvd);

Left Join(Log)

Load

Max(Periodo) as Periodo,

1 as BanderaAño

Resident Log

Group by Año;

Left Join(Log)

Load

Max(Periodo) as Periodo,

1 as BanderaQuarter

Resident Log

Group by Año,Quarter;

Measure:

if([BanderaAño]=1,Count({<[BanderaAño]={1}>}Id_Recurso),

if(BanderaQuarter=1,Count({<[BanderaQuarter]={1}>}Id_Recurso),

Count(Id_Recurso)

))

Re: Cum count error

oknotsen — Fri, 02 Jun 2017 07:20:30 GMT

Never mark a reaction to your own topic as the correct answer (marking removed). You received help from others; they gave you the correct and helpful answers. Be so kind to give credit where credit is due.