topic Re: Loading the Current Year and Previous Year in QlikView

Loading the Current Year and Previous Year

Anonymous — Mon, 04 Jan 2016 06:10:33 GMT

I need to show Number of Products sold each year for different Categories.

Something like this

Here Category will be the Primary Dimension and Year will be the Secondary Dimension.
count(Products Sold) will be the expression.

However, I need to show the count only for Current and Previous Year. Current Year data will be updated monthly. For that purpose there is a field called UpdatedDate.

Assume, Current Year is 2015 and Previous Year is 2014. 2014 data will be same while 2015 data changes every month. I need to the bar chart accordingly.

I tried this as a Calculated Dimension

If(

Year=(max(Year)-1), Year,

if(

Year=max(Year) and UpdatedDate=max(UpdatedDate),Year

)

This is not working. I know I'm wrong logically somewhere. Can someone help me on how to solve this?

Re: Loading the Current Year and Previous Year

Kushal_Chawda — Mon, 04 Jan 2016 06:19:23 GMT

Current :

=count({<Year ={"$(=max(Year))"}>}Products Sold)

Previous:

=count({<Year ={"$(=max(Year)-1)"}>}Products Sold)

Re: Loading the Current Year and Previous Year

tamilarasu — Mon, 04 Jan 2016 06:20:50 GMT

Try,

Aggr(Only({<Year={'$(=Max(Year))','$(=Max(Year)-1)'}>} Year),Year)

Re: Loading the Current Year and Previous Year

HirisH_V7 — Mon, 04 Jan 2016 06:22:48 GMT

Hi,

You can use two expression for comparison it will be easy ,

Dimension :

Re: Loading the Current Year and Previous Year

Anonymous — Mon, 04 Jan 2016 06:30:48 GMT

Hi Tamil,

This will work perfectly. But I need to include the max(UpdatedDate) for the Current Year since there is new data every month. Is there a way to do that?

Re: Loading the Current Year and Previous Year

tresB — Mon, 04 Jan 2016 06:31:33 GMT

Set analysis would be a better way to achieve this. Having said that, if you want to know what went wrong the way you tried and get it corrected, try like:

If(

Year=$(=max(Year)-1), Year,

if(

Year=$(=max(Year)) and UpdatedDate=$(=max(UpdatedDate)),Year

)

Re: Loading the Current Year and Previous Year

Anonymous — Mon, 04 Jan 2016 06:32:45 GMT

Hi Harish and Kush141087‌,

I tried this using two expressions. I guess I cannot make use of Legend selection. Correct me if I'm wrong.

Re: Loading the Current Year and Previous Year

tamilarasu — Mon, 04 Jan 2016 06:46:49 GMT

Fine.

=Aggr(Only({<Year={'$(=Max(Year)-1)'}>+<Year={'$(=Max(Year))'},UpdatedDate={'$(=Date(Max(UpdatedDate),"DD/MM/YYYY"))'}>} Year),Year)

Change the date format accordingly.

Re: Loading the Current Year and Previous Year

HirisH_V7 — Mon, 04 Jan 2016 06:58:38 GMT

Hi,

Check this App with sample data,

At Back end:

Data:
LOAD Category,
     [Updated Date],
     Month([Updated Date]) as Month,
     Year([Updated Date]) as Year,
     [Products Sold]
FROM
[\Dataforprecurr.xlsx]
(ooxml, embedded labels, table is Sheet1);

At Front end:

In Chart:

Dimensions:

Category and Year,

Expressions:

Previous and current year expressions

HTH ,

-Hirish.

Re: Loading the Current Year and Previous Year

sasikanth — Mon, 04 Jan 2016 07:06:11 GMT

hi,

Try something like

Create one variable

vYear=max(Year) then calculated dim will be like

if(Year>=($(vYear)-1) ,Year) and check the option Suppress Null values

Re: Loading the Current Year and Previous Year

Anonymous — Mon, 04 Jan 2016 07:29:39 GMT

Hi Tamil,

Unfortunately, the current Year data is not getting loaded. I'm not sure why it's happening. But the solution can be obtained using the technique tresesco‌ has given here. Even he mentioned, using set analysis is the better way.

Thanks.

Re: Loading the Current Year and Previous Year

Anonymous — Mon, 04 Jan 2016 07:31:56 GMT

Hi Hirish,

Thanks for your time. Using two expressions is good and I have tried this already. But the end users are very novice when it comes to qlikview. So the first thing they do is clicking the legend for Year selection.

Legends are not selected if expression is used even though they are displayed.

Thanks.

Re: Loading the Current Year and Previous Year

Anonymous — Tue, 05 Jan 2016 05:39:23 GMT

Hi tresesco‌,

If possible can you tell me how to include the updated date for previous year as well?

Say, 2015 is my Current Year and the max(UpdatedDate) should give me 2015's last Updated date.

Similarly 2014 is my previous year and the max(UpdateDate) should give me 2014's last updated date.

Literally a sub query kind.

I tried this in a plain text box to get the last updated date for Previous Year.

max({<Year={'$(=max(Year)-1)'}>}UpdatedDate)

Not Sure how to implement the same in Calculated Dimension.

Re: Loading the Current Year and Previous Year

tamilarasu — Tue, 05 Jan 2016 06:02:22 GMT

Hi Koushik,

Try,

=If(

Year=$(=Max(Year)-1) and UpdatedDate = $(=Max({<Year={$(=Max(Year)-1)}>}UpdatedDate)), Year,

If(

Year=$(=Max(Year)) and UpdatedDate = $(=Max(UpdatedDate)),Year

)