topic Re: How to code expression about YTD? in QlikView

How to code expression about YTD?

panwipa2528 — Mon, 18 Sep 2017 12:11:15 GMT

I need to compare year to date,

Now I do

for 2017 = Sum({$<Year = {$(#=(Year-1), Month=, Date=,)}, } Sales) >>> 1-15 Sep 2017

for 2016 = Sum({$<Year = {$(#=(Year-1), Month=, Date=,)}, } Sales) >>> 1-30 Sep 2016

but I need

for 2017 = Sum({$<Year = {$(#=(Year-1), Month=, Date=,)}, } Sales) >>> 1-15 Sep 2017

for 2016 = Sum({$<Year = {$(#=(Year-1), Month=, Date=,)}, } Sales) >>> 1-15 Sep 2016

Re: How to code expression about YTD?

PrashantSangle — Mon, 18 Sep 2017 12:26:31 GMT

Hi,

If you have date field

then try like

sum({<date={"=date(addYears(max(date),-1)))"}>}Sales)

Regards,

Re: How to code expression about YTD?

shraddha_g — Tue, 19 Sep 2017 07:22:02 GMT

Consider you have Date field in your Data Model &

Assume 2 Variable for From Date & To Date as vFromDate & vToDate respectively.

Then Try CY YTD

sum( {<Date = {">=$(=vFromDate) <= $(=vToDate)"}>}Sales)

For LY YTD

sum( {<Date = {">=$(=date(addyears($(vFromDate),-1))) <=$(=date(addyears($(vToDate),-1)))"}>}Sales)

Re: How to code expression about YTD?

panwipa2528 — Wed, 20 Sep 2017 06:46:59 GMT

Thank you for you help. Your answer is correct, but I have a problem if I choose year and date as attach file,
It's not work. How should I do?

Re: How to code expression about YTD?

panwipa2528 — Wed, 20 Sep 2017 15:01:53 GMT

stalwar1‌ Help me pleaseeeeee

Re: How to code expression about YTD?

sunny_talwar — Wed, 20 Sep 2017 15:05:31 GMT

Try this for last year

Sum({<Date = {">=$(=Date(AddYears($(vFromDate),-1)))<=$(=Date(AddYears($(vToDate),-1)))"}, Year, Month>}Sales)

Re: How to code expression about YTD?

panwipa2528 — Wed, 20 Sep 2017 15:26:14 GMT

Can not . stalwar1‌

Re: How to code expression about YTD?

sunny_talwar — Wed, 20 Sep 2017 22:15:46 GMT

Can you explain in words as to what is the issue?

Re: How to code expression about YTD?

panwipa2528 — Thu, 21 Sep 2017 02:33:47 GMT

If I select
1. Month = September & Year = 2017 > Result will show summary all month (1-30 Sep 2016)
2. Period 1-15 September 2017 > Result will show summary 1-15 Sep 2016

But this expression it's work only 2. (Select Period), I need both condition.

Sum({<Year, Month, date_key = {">=$(=Date(AddYears($(vDateFrom),-1)))<=$(=Date(AddYears($(vDateTo),-1)))"}>}Fact_Tran.Qty)

Re: How to code expression about YTD?

PrashantSangle — Thu, 21 Sep 2017 09:45:43 GMT

can you provide sample app???

Regards,