Cum count error

[Anonymous]

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

[Vasiliy_Beshentsev]

Hello, again.

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

[Anonymous]

Hi Vasily

I need to achieve this result

[Vasiliy_Beshentsev]

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;

[Anonymous]

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,

[Vasiliy_Beshentsev]

Add max(quarter)

[Vasiliy_Beshentsev]

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.

[Anonymous]

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

[Anonymous]

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)   

))

[oknotsen]

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.