topic Re: Special YTD formula calculation in QlikView

Special YTD formula calculation

Wed, 20 Jan 2016 21:51:09 GMT

Hi everyone

I need to compare last year with current year, but just those months that have sales last year. Additionally I have just Tickets and not canceled ones, and a flag for last year sales.

Example

On one store we just have sales on dic 2014 and on 2015 all year, but I need just to compare dic 2015 vs dic 2014.

My formula look like this

sum({$<

Fecha={">=$(=date(yearstart(max(Fecha))))<=$(=date(max(Fecha)))"},

Mes=P({1<Mes={'=Sum({$< Doc *= {"T*"} - {"TS*"}, TicketCancelado ={0}, Año={$(=year(addmonths(max(Fecha),-12)))" }, Flag ={1} >} Ventas )>0'}>} Mes )

>} Ventas)

Hope somebody could help me!

Re: Special YTD formula calculation

settu_periasamy — Thu, 21 Jan 2016 04:42:47 GMT

Looks like double quotes missing here -> {$(=year(addmonths(max(Fecha),-12)))" }

May be try this

=sum({$< Fecha={">=$(=date(yearstart(max(Fecha))))<=$(=date(max(Fecha)))"},

Mes=P({1<Mes={'=Sum({$< Doc *= {"T*"} - {"TS*"}, TicketCancelado ={0},

Año={"$(=year(addmonths(max(Fecha),-12)))"}, Flag ={1}

>} Ventas )>0'}

>} Mes )

>} Ventas)

Re: Special YTD formula calculation

jagan — Thu, 21 Jan 2016 05:08:47 GMT

Hi,

Check this link for similar type of expressions

Set Analysis for certain Point in Time

Regards,

Jagan.

Re: Special YTD formula calculation

Mon, 25 Jan 2016 17:51:08 GMT

Hi Settu

It was a typing error, but that formula does not reflejct the spected result.

Re: Special YTD formula calculation

Mon, 25 Jan 2016 17:53:22 GMT

Hi Jagan

Actually I don't have troubles with laps of time, is base on sales from an specific store, not periods of time.

Re: Special YTD formula calculation

jagan — Tue, 26 Jan 2016 00:55:00 GMT

HI,

Attach some sample data and your expected output so that it would be easier to understand and provide the solution.

Regards,

Jagan.