topic Set analysis expression for current/previous month not working in QlikView

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 10:34:40 GMT

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!!

Set analysis expression for current/previous month not working

jagannalla — Tue, 15 May 2012 10:47:42 GMT

Chrisgoudy,

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

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 10:54:55 GMT

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..

Set analysis expression for current/previous month not working

kaushiknsolanki — Tue, 15 May 2012 11:04:58 GMT

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

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 11:08:29 GMT

Kaushik

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

Doesn't have it where?

Set analysis expression for current/previous month not working

kaushiknsolanki — Tue, 15 May 2012 11:12:10 GMT

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

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 11:13:10 GMT

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

Set analysis expression for current/previous month not working

kaushiknsolanki — Tue, 15 May 2012 11:17:16 GMT

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

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 11:21:31 GMT

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.

Set analysis expression for current/previous month not working

kaushiknsolanki — Tue, 15 May 2012 11:23:11 GMT

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

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 11:30:41 GMT

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

returned Aug-2011

Set analysis expression for current/previous month not working

kaushiknsolanki — Tue, 15 May 2012 11:34:24 GMT

Hi,

It means your %_DateKey is having max date for Oct-2011

am I right..?

Regards,

Kaushik Solanki

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 11:37:01 GMT

Sorry, I'm not sure if I know how to do what you're asking.

I have opened to a sheet with a chart that shows sales for each month.

Does that work okay for you if I upload it like that?

Set analysis expression for current/previous month not working

kaushiknsolanki — Tue, 15 May 2012 11:40:14 GMT

Ok No Problem Also if you can tell me in what format you want the data.

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 11:45:26 GMT

%_DateKey has the max date Sep-2011

I uploaded a couple files since I wasn't sure.

I'm not sure what format I want it in.

Can you explain??

all I know is I need the Set Analysis expression for current and previous months sales.

are there different formats to do that?

Re: Set analysis expression for current/previous month not working

kaushiknsolanki — Tue, 15 May 2012 11:57:29 GMT

HI,

Create a chart with dimension as Pharmacy Group and expression1 as

Sum({<MonthYear = {"$(=MaxString(%_DateKey))"}>}Sales)

Expression2 as

sum({<MonthYear = {"$(=Date(Addmonths(Max(%_DateKey),-1),'MMM-YYYY'))"}>}Sales)

Expression1 for current Month and Expression 2 for Previous Month

For more have a look at the application attched.

Regards,

Kaushik Solanki

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 12:06:41 GMT

Expression 1 isn't working for me. It returns a value of 0

Expression 2 works if nothing is selected, but once I select a month, it returns a value of 0

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 12:09:04 GMT

I can see it works in the model you uploaded.

Do the expression you used only work in charts?

I need it in charts, but also other places, like text boxes that allow a user to compare current and previous months sales

Re: Set analysis expression for current/previous month not working

kaushiknsolanki — Tue, 15 May 2012 12:11:56 GMT

Hi,

It is working in Chart at my side.

And it should also work in the textbox.

Can you please send me the screen shot of that sheet with the selection where you are getting zero.

Regards,

Kaushik Solanki

Set analysis expression for current/previous month not working

Anonymous — Tue, 15 May 2012 12:20:38 GMT

I took snapshots and attached them.