Skip to main content

# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Search instead for
Did you mean:
Creator III

## Loading the Current Year and Previous Year

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?

1 Solution

Accepted Solutions
MVP

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

)

)

13 Replies

Current :

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

Previous:

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

Champion

Try,

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

Master

Hi,

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

Dimension :

Category

Expressions:

Previous Year

Count({< Year={\$(=Max(Year)-1)}>}Products Sold)

Current year

Count({< Year={\$(=Max(Year))}>}Products Sold)

Post your sample data if possbile.

HTH,

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
Creator III
Author

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?

MVP

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

)

)

Creator III
Author

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.

Champion

Fine.

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

Change the date format accordingly.

Master

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.

HirisH
“Aspire to Inspire before we Expire!”
Master

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

Community Browser