Set analysis expression for current/previous month not working

[Anonymous]

I have this expression, but it doesn't show the max YearMonth Sales if nothing is selected, it shows all sales for the year

sum({$<YearMonth={$(=Max(YearMonth))}, 

Year, 

Quarter, 

YearMonth>}Sales)

Likewise the expression for the Previous YearMonth doesn't work either. It shows the same data as the Max YearMonth

sum({$<YearMonth={$(=Max(YearMonth)-1)}, 

Year, 

Quarter, 

YearMonth>}Sales)

Please Help!

Thank you!!

[jagannalla]

Chrisgoudy,

Why are you using Year,Quarter,YearMonth in set analysis? Please attach your sample file it really helps us to solve.

[Anonymous]

I've attached it above

I'm pretty new to Qlikview, so I'm using those fields because it was recommended by another Qlikview user..

[kaushiknsolanki]

Hi,

    First thing your QVW file doesnt have the table MasterCalander eventough its their in script.

    According to your calculation for MonthYear you can find the previous MonthYear as below.

    =Date(addmonths(Max(Date),-1),'MMM-YYYY')

    Hope this helps

Regards,

Kaushik Solanki

[Anonymous]

Kaushik

Not sure I understand what you mean that my QVW doesn't have the table MasterCalendar??

Doesn't have it where?

[kaushiknsolanki]

HI,

    If you open your attachment, you will see that you dont have the table which gives me the MasterCal Fields like MonthYear.

   You can check the data model, you wont find the table there too.

   Make sure that the document is properly reloaded and then Upload.

Regards,

Kaushik Solanki

[Anonymous]

Kaushik

=Date(addmonths(Max(Date),-1),'MMM-YYYY')

doesn't work, my model doesn't recognize the field DATE in bold above, since I don't have a field called date I think

[kaushiknsolanki]

Hi,

    Yes you are right cause you dont have field called Date, if you closely look at the script which you have written, you will find that the Date field which I am saying is named in your script as %_DateKey and %_DateKeyCA

Regards,

Kaushik Solanki

[Anonymous]

sorry about that, i reloaded the model and uploaded again above.

so you are saying to use

=Date(addmonths(Max(%_DateKey),-1),'MMM-YYYY')   ??

this doesn't return what I want either. Sorry I wasn't clear about what I wanted. I'm looking for the set analysis that will give me the sales for the current and previous months.

[kaushiknsolanki]

HI,

   Please upload the QVW with newly loaded data and keep only one sheet with chart which shows the current month sales.

   I will tell you how to get the data for previous month.

Regards,

Kaushik Solanki