Calculating Month-over-Month and Yoy Based on current selection

[Anonymous]

Hey guys,

I am working on this app in qlik sense wherein i have chart with a column x, % change x MOM, % change x YOY

In my load script i have the date

Load "x",

date(date,'MMM-YY') as "month_year",

"web_site"

( I have other columns but they are not relevant to the question)

In my sheet i have a Filter Pane with month_year as the dimension

So the way i want it to be is that firstly when user selects a month-year from the filter pane. the chart should populate with entries as following:

website - x - MOM% change in X - YOY% change in x

Here web_site is a dimension rest all are measures.

I tried using various formulae for MOM from the qlik community but doesn't seem to work.

What my best attempt was using set analysis:

Sum({$<month_year= {"<=$(=date(getfieldselections(month_year)))"}>})total_unique_visitors for the current month

and using the same expression but just month_year - 1 for previous month.

However this does not result in any value. Can anyone please point out the error here. or suggest an easier approach

Thanks

[Anonymous · Accepted solution]

Hi

If you are looking for previous month data

try following

Sum({$<[month_year]={"$(=Date(AddMonths(Max(month_year), -1), 'MMM-YYYY'))"}>} total_unique_visitors)

For current month:

Sum({$<[month_year]={"$(=Date(Max(month_year), 'MMM-YYYY'))"}>} total_unique_visitors)

[Anonymous]

Hi

For MOM  use this ...

Sum({$<[month_year]={">=$(=Date(AddMonths(Max(month_year), -1), 'MMM-YYYY')<=$(=Date(Max(month_year), 'MMM-YYYY'))"}>} total_unique_visitors)

[Anonymous]

For a MOM, I used the current month - previous month / previous month and it works

However i cant seem to get the expression in the last post to work.

Is there a AddYears function as well similar to addmonths for YoY or do i have to do something differently.

[Anonymous]

for previous year's this month data ,,,use this

Sum({$<[month_year]={"$(=Date(Addyears(Max(month_year), -1), 'MMM-YYYY'))"}>} total_unique_visitors)

[Anonymous]

Awesome! I just did -12 for year and it seems to work. Thanks for the prompt reply

galax_allu‌ Thank you

[Anonymous]

hi

please mark answered , if you are okay with the solutions....

[Anonymous]

Hi Allu.

For your MOM solution, I tested it is blank/