Previous Month vs. Current Month

[mfarsln]

Hi!

I have an excel file which has some measures with year and month dimensions. I want to do some monthly basis analyzes. I have month and year as filter criteria. What i want to do is that if user selects a month from filter panel, that month's values and previous month's values to be shown at the same table row.

Desired result is like that;

I'm sharing very very basic of my data and qvf.

[sunny_talwar · Accepted solution]

Try these expression

Current Month

Sum(VALUE)

Previous Period

Above(Sum({<MONTH>}VALUE)) * Avg(1)

[mfarsln]

Thanks for reply. It's working in the sample qvd. But in my real app it gives '-'.
I've added two more set analysis criteria to previous period.

Above(Sum({<MONTH_=,ROOT_FACILITY_ID={"6"},YEAR={"2017"}>}VALUE)) * Avg(1)

With this measure, if i don't select any month and year it gives the previous month values for each month. But when i select month it gives only selected month's values. Other months still displayed as nulls.

Here is my qvf which contains my real data. You can check the sheet2.

[sunny_talwar]

You can remove the null data by unchecking 'Include zero values' under Add-ons -> Data handling

[mfarsln]

Thank you for all the time you've spend for me.

Now i have another small problem. I finally need to analyze data for given month. And i don't want to do this by listing current month and previous month's values on seperate columns. I want to list visitor counts in one column, and in the rows i want to see my selected month and previous month of it.

This is a basic demonstration of what's inside my mind:

I think i have to create synthetic dimensions with ValueList() function. But the point that i'm stucked is renaming these dimensions based on the selected month. For example, if i select May from months, it should be Apr 2017 and May 2017.

This is what i've tried so far.

$(currMonth)=GetFieldSelections(MONTH_)
$(prevMonth)=GetFieldSelections(MONTH_)-1

if(
ValueList(
pick(match($(currMonth),1,2,3,4,5,6,7,8,9,10,11,12),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') & chr(160) & '2017',
pick(match($(prevMonth),1,2,3,4,5,6,7,8,9,10,11,12),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') & chr(160) & '2017')="Apr 2017"
,sum({<ROOT_FACILITY_ID={"6"},YEAR={"2017"},MONTH_={"4"}>}VISITOR)
,sum({<ROOT_FACILITY_ID={"6"},YEAR={"2017"},MONTH_={"3"}>}VISITOR)
)

But this always goes to the else block. Tried with single quotes but it didn't work either. I couldn't find what's the problem. Even if i find the problem and resolve it, do i have to repeat this logic for all months? Is there an easier way to achieve this?

Edit: Okey i've fixed the if problem by using single quotes and getting first 3 characters of the valuelist. But my last question is still waiting to be answered, hopefully 🙂

Edit-2: So i think i found the solution by using or operator inside the set analysis of my measure.

SUM({1< ROOT_FACILITY_ID={6},YEAR={2017},MONTH_={$(=$(prevMonth))} > + < ROOT_FACILITY_ID={6},YEAR={2017},MONTH_={$(=$(currMonth))} >}VISITOR)

[ThePeterK]

This isn't working for me:

Above(Sum({<[MONTH]>}[Headcount])) * Avg(1)

Here, I'm trying to get the value of 'Headcount' for the previous month. It has 4 other dimensions involved (Level, Source, Materials), if that's relevant.

Current Month works fine.

[ysalvi]

Hello @sunny_talwar 
Tried your approach it is not working for me, when I filter the data for the Current month selection it appears as blank 

Thanks in advance