topic Re: set analysis for date in QlikView

set analysis for date

Mon, 28 Jul 2014 10:03:08 GMT

Hi,

I am trying to make a table with data for the past 5 days. This is the expression I am using:

Sum( {$< EventDate = {'$(vMaxDate)'},Account= -{'500','400'} >} Revenues).

What is the correct way to write this expression so that I have vMaxDate-1, vMaxDate-2......vMaxDate-5 in the set analysis? I have tried having the "-number" in different places in the equation but I keep on getting 0. Help appreciated.

Cheers.

Re: set analysis for date

CELAMBARASAN — Mon, 28 Jul 2014 10:10:08 GMT

Try kind of this

Sum( {$< EventDate = {'>$(=Date(vMaxDate-5))<=$(=vMaxDate)'},Account= -{'500','400'} >} Revenues)

Re: set analysis for date

MK_QSL — Mon, 28 Jul 2014 10:15:17 GMT

=SUM({<EventDate = {">=$(=Date($(vMaxDate)-4,'DD/MM/YYYY'))<=$(vMaxDate)"},Account= -{'500','400'}>}Revenues)

Re: set analysis for date

Mon, 28 Jul 2014 10:25:11 GMT

didn't work, still getting no values

Re: set analysis for date

Mon, 28 Jul 2014 10:25:17 GMT

didn't work, still getting no values

Re: set analysis for date

Mon, 28 Jul 2014 10:25:45 GMT

didn't work, still getting no values

Re: set analysis for date

MK_QSL — Mon, 28 Jul 2014 10:26:37 GMT

What is the definition of your vMaxDate?

Re: set analysis for date

Mon, 28 Jul 2014 10:33:30 GMT

= If(WeekDay(Max(EventDate)) = 'Sat', Date(Max(EventDate)-1),

If(WeekDay(Max(EventDate)) = 'Sun', Date(Max(EventDate)-2),

Date(Max(EventDate))))

We have no data on saturday and sundy. the date is in this format: 25.07.2014

Re: set analysis for date

MK_QSL — Mon, 28 Jul 2014 10:36:38 GMT

Try this....

=SUM({<EventDate = {">=$(=Date($(vMaxDate)-4,'DD.MM.YYYY'))<=$(vMaxDate)"},Account= -{'500','400'}>}Revenues)

Re: set analysis for date

aveeeeeee7en — Mon, 28 Jul 2014 11:09:41 GMT

Try something like this:

Sum({<EventDate={'$(=(vMaxDate))'},Account={'*'}-{'500','400'}>}Revenues)

Regards

Aviral Nag

Re: set analysis for date

Mon, 28 Jul 2014 11:17:09 GMT

Try:

Sum( {$< EventDate = {'$(=Date($(vMaxDate))-5)'},Account= {'*'}-{'500','400'} >} Revenues).

Re: set analysis for date

PradeepReddy — Mon, 28 Jul 2014 11:22:17 GMT

can you share the application?

Re: set analysis for date

Mon, 28 Jul 2014 11:33:16 GMT

this doesn't work

Re: set analysis for date

Mon, 28 Jul 2014 11:39:34 GMT

Hi Nag,

This returns the default value which I already have ie MaxDate. However, in the case of MaxDate-1,-2....-5, the value returned is 0.

Re: set analysis for date

MK_QSL — Mon, 28 Jul 2014 11:43:18 GMT

Provide sample data...

Re: set analysis for date

Mon, 28 Jul 2014 11:44:45 GMT

Hi Jose,

This returns a zero value to my table.

Re: set analysis for date

Mon, 28 Jul 2014 11:47:05 GMT

Pradeep, unfortunately I don't know how I can share the application due to it's size, data model, sources and also without leaking sensitive info...

Re: set analysis for date

MK_QSL — Mon, 28 Jul 2014 11:49:32 GMT

You can create some dummy data similar to your apps and share in excel file along with the output you required.

Re: set analysis for date

Mon, 28 Jul 2014 11:53:36 GMT

Based on the variable you shouldn't need that second dollar expand, I think the below should work

=SUM({<EventDate = {'>=$(=Date(vMaxDate-4))<=$(vMaxDate)'},Account= -{'500','400'}>}Revenues)

hope that helps

Joe

Re: set analysis for date

Mon, 28 Jul 2014 12:54:36 GMT

Hi Joe,

This expression gives me the sum of all revenues for 5 days. However I would like to have

Day 1: xxxx

Day 2: yyyy

Day 3: zzzz.

The formula as is does work though