Previous Year problem again

[AH]

Hi All,

I have Month, Year and Sales field in an excel file. Month field contains values like 'January', 'February' in this

format. Year field contains values like '2014', '2015', '2016' in this format. I would like to make the Month field

values like 'Jan', 'Feb', 'Mar' this format. for this i used Capitalize(left(Month,3)) as [Sales Month].

I Used Date#(Capitalize(left(Month,3)) & ' ' & [Year], MMM YYYY) as [Sales Month Year]. Then I created a variable

vPreviousMonthYear(Date(addmonth(max([Sales Month Year]),-12), 'MMM YYYY') out of the [Sales Month Year] field.

The purpose of creating the variable 'vPreviousMonthYear' is to use it in the calculation of

1) Previous year sales, meaning that, if the user select 2016 from the year field then they can also see the 2015

sales as the previous year sales, if they select 2015 that would be the current and 2014 would be the previous sales

when the user select Year as 2016 and month as January the user will see the current year as 2016 and month as January

sales But they also see the previous Year as 2015 and Month as January from Year 2015

My calculation for previous year calculation is: sum({<Sales Month Year>={'$(vPreviousMonthYear'}'}, [Sales Year]=, [Sales Month]=>}Sales)

One of my previous post with the same problem,  @Sunny T answered  the post and that was helpful but when i tried to

resolve the real problem i have i am getting problem/issues.

Can anyone help me solving the problem?

Thanks & regards,

ahmed100

[settu_periasamy]

Are you getting proper value for 'Sales month year ' value and vPreviousMonthYear value?

If so, try like

sum({<[Sales Month Year]={'$(vPreviousMonthYear)'}, [Sales Year]=, [Sales Month]=>}Sales)

[AH]

‌Hi, Settu,

i think i am using the same calculation you mentioned. My guess is i having issue with the variable I created for previousmonthyear.

Thanks,

ahmed100

[settu_periasamy]

Your posted expression seems to be incorrect syntax..

Can you post exact syntax you are using?

And check your variable giving the correct rest? Put the variable in textbox and check it..

If still have problem, can you post the sample?

[sunny_talwar]

I guess my question is what is your dimension here? Is it Year dimension dimension? If it is then you won't be able to just use set analysis to get the result on the same line. For that you will have to use Above() or Below() function. I think if you can provide a sample which somewhat matches what your real scenario is, we might be able to help you better

[AH]

Hi Settu & Sunny,

Thanks for your responses. I am sorry not to add a dimension to my example. I just added  the customer dimension to it. Please see the attach file.

Settu, You are right! there is syntax errors in the calculation that i posted for the previous year calculation. But the original calculation i made in qlikview is same as you. So, i guess there is no issue on that. I am suspecting that that the way i created the PreviousMonthYear variable is not the right way to calculate it and thats why its not working in this particular situation i am trying to handle.

vPreviousMonthYear = (Date(addmonth(max([Sales Month Year]),-12), 'MMM YYYY')

Oh another thing, i was offline for quite a long time, thats why i couldn't reply post as soon as you guys reply my post.

Please let me know if you have any question / if i am vague of putting forth information.

Thanks & regards,

ahmed100

[sunny_talwar]

Are you hoping to see something like this?

Expression for Previous Year Sales (PY Sales):

=If(Sum(Sales) > 0, Above(Sum({<Year, Month>}Sales), 12))

[AH]

Hi Sunny,

Thanks for the reply and the solution. The apps you attached works perfectly fine and its working as expected. But when i apply that in my real application it does not work for some reason. I just couldnt find the reason why it shouldnt work. Could you please give me some suggestions based on your experience where i am doing wrong what could possibly go wrong in this particular situation? Even though i totally understand that without looking at the real apps its quite impossible to say whats going wrong with the apps.

Btw, Could you please explain what is this expression doing to calculate the Previous Year sales?

=If(Sum(Sales) > 0, Above(Sum({<Year, Month>}Sales), 12))

Thanks,

ahmed100

[sunny_talwar]

Another solution could be creating an As-of Table. Here is the script:

Table:

LOAD Customer,

  Month(Date#(Month, 'MMMM')) as Month,

    Year,

    Date(MonthStart(Date#(Month & ' ' & Year, 'MMMM YYYY')), 'MMM YYYY') as Date, 

    Sales

FROM

[Data (5).xlsx]

(ooxml, embedded labels, table is Sales);

Calendar:

LOAD Date as As_Of_Date,

  Date,

  'CY' as Flag

Resident Table;

Concatenate(Calendar)

LOAD Date as As_Of_Date,

  Date(AddYears(Date, -1)) as Date,

  'PY' as Flag

Resident Table;

Data model:

See if this solution is easier for you to implement. I can go over in details for the other if this doesn't work.

[AH]

Hi Sunny,

Thanks again for your reply. The attachment you posted works perfect but i cant make that work in my original application. I dont understand why its not working. Could you copy your email here? May be i could show you a fraction of the original data and you could tell me the issue. Please ignore this message if you dont share email.

I appreciate your help.

Thanks,

ahmed100