topic Re: Previous Year on Straight table in QlikView

Previous Year on Straight table

jjking58 — Wed, 25 Nov 2020 16:16:04 GMT

Can someone explain how to get the previous ytd based on a year/month selection.

When I click on Dec.2016. I'm getting the entire month of Dec 2015 instead of YTD

The code is below for current and previous

Thanks I greatly appreciate it

Jim

Re: Previous Year on Straight table

vishsaggi — Wed, 21 Dec 2016 21:16:41 GMT

Are you using a calendar script in your dashboard. ? Can you share a sample app if possible ?

Re: Previous Year on Straight table

jjking58 — Wed, 21 Dec 2016 21:22:10 GMT

No i'm not using a calendar script. i took a field within a data base called IHIDAT and separated it into month/year/day

using the qlikview functions(month,year,day).

Re: Previous Year on Straight table

joseduque — Wed, 21 Dec 2016 21:22:45 GMT

Hi,

If you dont want the other selections make changes to your data use 1 in the set analysis. Ex:

sum({1<IDPAGR={'MECH'},Year={"$(=max(Year)-1)")}>}

Also you forget to use "" in the YTD sentence "$(=max(Year)-1)"

Re: Previous Year on Straight table

vishsaggi — Wed, 21 Dec 2016 21:32:07 GMT

What is your expected output for YTD here? Can you share a sample app with some scrambled data?

Re: Previous Year on Straight table

vishsaggi — Wed, 21 Dec 2016 22:32:41 GMT

If possible try the script attached with this and let me know how it works?

Re: Previous Year on Straight table

varshavig12 — Thu, 22 Dec 2016 03:52:07 GMT

For YTD, you have to give a date range.

Re: Previous Year on Straight table

neha_shirsath — Thu, 22 Dec 2016 10:37:55 GMT

Hi Jim,

Please see the below link for CYTD and LYTD.

YTQ, QTD, MTD and WTD

Thanks & Regards,

Neha

Re: Previous Year on Straight table

Michiel_QV_Fan — Thu, 22 Dec 2016 11:59:26 GMT

Jim, find the qlikview components (GitHub - RobWunderlich/Qlikview-Components: A library for common Qlikview Scripting tasks) on the internet and use the calendar generation script from there. Besides a default calendar it can also generate the set analysis statements you need for selecting specific time frames.

Re: Previous Year on Straight table

jjking58 — Thu, 22 Dec 2016 13:44:47 GMT

Neha,

I tried using the formulas from this post. which are listed below. The CYTD works,but the PYTD is not giving me totals and show the dates from CYTD.

IHIDAT is in format YYYYMMDD and the Dateformat in the script is also set to YYYYMMDD. Does this make a difference ?

CYTD

Sum({<IDPAGR = {'EQUP'},OrderType-={'TI'}, Year=, Month=, IHIDAT={">=$(=Num(YearStart(Max(IHIDAT))))<=$(=Max(IHIDAT))"},NOAGRP-={'INTR'}>} Amount)

PYTD

Sum({<IDPAGR = {'EQUP'},OrderType-={'TI'}, Year=, Month=, IHIDAT={">=$(=Num(YearStart(Max(IHIDAT),-1)))<=$(=AddYear(Max(IHIDAT),-1)"},NOAGRP-={'INTR'}>} Amount)

Please advise,

Jim