Loading the Current Year and Previous Year

[Anonymous]

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?

[Kushal_Chawda]

Current :

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

Previous:

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

[tamilarasu]

Try,

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

[HirisH_V7]

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

[Anonymous]

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?

[tresesco · Accepted solution]

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

            )

  )

[Anonymous]

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.

[tamilarasu]

Fine.

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

Change the date format accordingly.

[HirisH_V7]

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.

[sasikanth]

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