topic Re: Set analysis, sum yesterday in QlikView

Set analysis, sum yesterday

Fri, 20 Feb 2015 09:36:26 GMT

So now I've got the below two separate working formulas, how do I put them together? My set analysis skills are poor, I never seem to learn the syntaxes

TODAYS

=SUM(IF(TYPE='XXX',AMOUNT))

BUT NEED TO USE YESTERDAYS DATE

IF(WeekDay(Max(Date)) = 'Mon', Date(Max(Date) - 3), Date(Max(Date) -1))

Re: Set analysis, sum yesterday

mukesh24 — Fri, 20 Feb 2015 09:47:42 GMT

Use Following syntax:-

sum( {$<Date= {$(#=Max(Date)-1)}>} Amount)

Regards,

Mukesh Chaudhari

Re: Set analysis, sum yesterday

Fri, 20 Feb 2015 09:50:45 GMT

Hi Mukesh,

can you pls specify an example with my if condition (IF TYPE) as well?

Thanks in advance,

Olle

Re: Set analysis, sum yesterday

PrashantSangle — Fri, 20 Feb 2015 09:59:55 GMT

Hi,

Try like

for today

sum({<Type={"XXX"},DateField={"=date(Today())"}>}Amount)

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)))"}>}Amount)

for Yesterday

sum({<Type={"XXX"},DateField={"=date(Today()-1)"}>}Amount)

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)-1))"}>}Amount)

Regards

Re: Set analysis, sum yesterday

Fri, 20 Feb 2015 10:24:30 GMT

Hi Max,

I think I need to rephrase my request. I need to sum the previous date, meaning If current date is monday I need to show the sum of fridays amounts.

Hence my formula for this purpose:

IF(WeekDay(Max(Date)) = 'Mon', Date(Max(Date) - 3), Date(Max(Date) -1))

When trying your formula below Im getting only zero values.

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)-1))"}>}Amount)

Re: Set analysis, sum yesterday

PrashantSangle — Fri, 20 Feb 2015 10:32:02 GMT

Hi,

Try like

if(weekday(Date)='Mon',

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)-3))"}>}Amount),

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)-1))"}>}Amount)

)

Regards

Re: Set analysis, sum yesterday

Fri, 20 Feb 2015 10:37:57 GMT

still getting zero values im afraid

Re: Set analysis, sum yesterday

PrashantSangle — Fri, 20 Feb 2015 10:45:47 GMT

Hi,

Debug your set analysis expression

1)convert your chart to straight table.

2)Write above expression.

3)dont write label

4)Press Ok

5) Once chart created , take your arrow to header of that expression.

Check what value it is showing for Datefield=

If possible post screen shot of it.

Regards

Re: Set analysis, sum yesterday

Anonymous — Fri, 20 Feb 2015 10:53:37 GMT

is it just a mistake in your statement, but on the left you write "Datefield" (upper D) and in the right expression you say

max(datefield) (lower d).

Instead of using if statement and test for monday you may use

firstworkdate(datefield,1) which delivers the previous workdate

and it works for public holidays as well (normally you will have no sales on a public holiday, except your shop is opened)

Re: Set analysis, sum yesterday

Fri, 20 Feb 2015 10:58:03 GMT

Hi Rudolf,

that is a neat solutuion, however Im doing this for different currencies/countries with different holidays.

So I need something more dynamic

Kind REgards,

Olle