New user needing help with period to date

[#1]

Hi,

I'm an absolute newbie at this and about to make a complete fool of myself I expect but...

I have a table showing total sales this year for a week, total sales last year for the same week, and I also want period to date for this year and last year.

Week, period and year are all variables named ThisWeek, ThisPeriod and ThisYear (ther is also one for LastYear)

Currently the expression looks like this:

Sum (IF(DWH_OP_WEEK <=ThisWeek

AND DWH_OP_DATE_YEAR=ThisYear

AND DWH_OP_DATE_PERIOD=ThisPeriod,SALES_IM_SALES_VALUE_GROSS))

It works fine for period to date at the end of the period, but if I want to look back at week 2 period 1 for example, it still shows me total Period To Date for the whole period (when i only want to see the Period to Date up to week 2).

Any suggestions would be amazing!

Thanks

[RedSky001]

Hi jarrold530,

It would be easier to help you if you uploaded a qlikview document. 

This would be usefull for people trying to understand how far you've currently got.

Next provide some examples of what you're expecting to see.

If you follow that method for posting questions you'll probably find people reply a lot sooner.

Mark

[#3]

Hi,

Thanks for the advice. Unfortunately my file is far too big to upload and I don't know hoe I can make it any smaller.

Put simply, I want to be able to:

sum the sales where...

the year against the transaction = the year in my variable,

and the period against the transaction = the period in my variable,

and the week against the transaction is less than or equal to the week in my variable.

I want to be able to say 'in week 3 of period 2 2013 the sales in the period up to that point were...'

At the moment, whatever week I put in it gives the total for the whole period, not the total up to that point.

My current expression looks like this:

SUM(IF(DWH_OP_DATE_YEAR=ThisYear

AND DWH_OP_DATE_PERIOD=ThisPeriod

AND DWH_OP_WEEK<= ThisWeek, SALES_IM_SALES_VALUE_GROSS))

To confuse matters slightly we use a financial calendar rather than a standard date calendar, but this is loaded in from a spreadsheet and works fine for everything else.

Does anyone have any ideas on how the expression should look.

Sorry for my complete ignorance but I have only been using Qlikview for a couple of days and already the powers that be are demanding their reports!

Any help would be hugely appreciated, as i have trawled through the help file and hundreds of posts on here and haven't found an answer which sorts my problem.

Many thanks in advance,

Matthew

[julian_rodriguez]

You need to use a Maste Calendar table, and then Set Analysis.

The easiest way to do that for me was using this component: http://community.qlik.com/thread/40043

Look at the calendar examples, I think that it's what you need.

Regards

[RedSky001]

As Julian says using a master calender will help.

I've not used the component he mentions. 

To do it the old fashioned way take a look at the sample I knocked up.