topic Re: Sum sales, group by date in QlikView

Sum sales, group by date

JustinDallas — Thu, 30 Jul 2015 20:16:23 GMT

Hello Everyone,

So I have a chart with 2 dimensions, and a Date dimension running across the top. The sum of sales is my Expression. Unfortunately, what's happening in my chart is that every Territory is having every Date show up in the chart. The end result is that I have multiple dates when I only really need one. Also, I am restricting the dates to 7 days in the past. My calculated expression for my dates looks like so:

=if(Date >= today()-7 and Date < today(), Date)

And my busted chart looks like so:

I've highlighted the Dates that I am being terrorized by.

Any help is greatly appreciated.

Re: Sum sales, group by date

Anonymous — Thu, 30 Jul 2015 21:01:58 GMT

instead of doing that in the dimension do that in set analysis. It best practice. And how to do that is...create a variable

which says =today()-7

in your set analysis you can write ====

sum({<datefeild={'>=$(vPYseverdays)'}>} sales)

Re: Sum sales, group by date

Gysbert_Wassenaar — Fri, 31 Jul 2015 05:39:16 GMT

Make sure your Date field doesn't contain timestamp values. Merely displaying a timestamp value as a date doesn't make the time part go away. You can check for time fractions by adding a listbox with the Date field and setting the Number representation to for example Fixed with 2 or more decimals. You can use the floor() function in the script to cut of the time fractions of a date field: Floor(Date) as Date.