topic Re: Date Issues in QlikView

Date Issues

Sat, 16 Mar 2013 00:09:41 GMT

Hi,

Here is my problem, i have a sales table that started last year, and i want to compare sales from this year with the year before in two ways, the first like going exactly 365 days back and the other pointing to a specific date in the past, The table goes like this:

Date

Sales_Amount

Store_Id

Equivalent_date

The last field would be the equivalent day to compare (to solve issues arround feb 29 or special day sales).

Im trying something like:

=sum({$<Equivalent_date={"=$(Date)"}, Date=>} Sales_Amount)

It just dont work

Thx in advance.

Re: Date Issues

Sat, 16 Mar 2013 04:41:59 GMT

Hi fernando,

You want to show max year and previous year comparision am i right then conver the date as a year. Can you send that app i will do your need.

Regards,

Nirmal

Re: Date Issues

khadeer — Sat, 16 Mar 2013 10:14:07 GMT

Actual Year

sum({<Year={$(=max(Year))}>} Sales_Amount)

Last Year

sum({<Year={$(=max(Year -1))}>} Sales_Amount)

Re: Date Issues

Gysbert_Wassenaar — Sat, 16 Mar 2013 14:26:44 GMT

You could try this:

=sum({$<Equivalent_date={'$(=only(Date))'}, Date=>} Sales_Amount)

Note, if you want to use Date or Equivalent_date as dimension then set analysis won't work. You'll have to create another table:

Sales:

Load Date, Store_id, Amount, Equivalent_date,

autonumber(Store_id&Equivalent_date) as Key

Sales_LY:

Load autonumber(Store_id&Date) as Key,

Amount as Amount_LY

resident Sales;

Now you can use Date and Store_id as dimensions and as expressions sum(Amount) and sum(Amount_LY)

Re: Date Issues

makkemik — Mon, 28 Apr 2014 09:26:33 GMT

So how can I find rolling -365 days sales with max selection of year and/or month ?