topic Re: Year to Date based on current selection (including multiple Year selection) in QlikView

Year to Date based on current selection (including multiple Year selection)

Thu, 21 Jun 2012 08:31:43 GMT

Hi All,

Ok, i am trying to create a straight table - 3 expressions:

1st showing the current YearMonth selection's Sales,

2nd showing the same as 1st except 12 months prior,

3rd would be a variance % between the 2.

I am familiar with using the only(Year) and max(Year) functions but this needs to be more dynamic. The client only has the Option to select YearMonth (201112, 201201, 201202 etc).

eg. If the client selects 201112 to 201203 the first collumn shows his current selection Sales, the 2nd (my problem) should show that selection's Sales value for 12 months before (201012 to 201103 Sales). Please let me know if i'm not being clear enough.

Thanks.

Re: Year to Date based on current selection (including multiple Year selection)

Anonymous — Thu, 21 Jun 2012 09:37:49 GMT

Hey Richard,

Try searching the forums for Set Analysis and you'll find plenty of examples of comparing data sets like this.

Re: Year to Date based on current selection (including multiple Year selection)

chematos — Thu, 21 Jun 2012 13:54:37 GMT

Try this, but I´m not sure if this works selecting several YearMonth... usually I use it selecting only one period time.

2º sum({<YearMonth={'>=$(=AddMonths(YearMonth,-12))'}>}Sales)

Regards

Re: Year to Date based on current selection (including multiple Year selection)

Thu, 26 Jul 2012 09:41:02 GMT

Hi Jose,

Sorry this is much later than the original post but i've revisited this topic. I've come very close after hours of playing around with formats etc. I've come to this as the previous Year Expression:

sum({$<Month=, Year=, YearMonth = {">$(=Year(Date(Date#((min(YearMonth)),'YYYYMM'),'YYYYMM'))&num(Month(Date(Date#((min(YearMonth)),'YYYYMM'),'YYYYMM')),'00')) <= $(=Year(Date(Date#((max(YearMonth)),'YYYYMM'),'YYYYMM'))&num(Month(Date(Date#((max(YearMonth)),'YYYYMM'),'YYYYMM')),'00'))"}>} Sales)

The figure comes close (less than it should be) but i guess a miss is as good as a mile.

Please let me know if you see anything here that shouldn't be.....

Regards