topic Re: Exclude total for one date in QlikView

Exclude total for one date

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

We were showing the business starts from every last day of month end to till date and now we were doing normal total.

But the businesses want to exclude the month end and include the total starts from 1st of month.

Below is the output qlikview which need to exclude the first row value in the total.

Dimension: =if(Date>='$(vMonthEnd)' and Date <='$(vCurrMonth)',Date)

where vMonthEnd = (monthend(addmonths(Date(today(),'MM/DD/YYYY'),-1)))

vCurrMonth = =(MonthEnd(today()))

Expression : Sum(Product$)

Chart Type : Straight Table

So the final total should show as $21,600,243 - $3,841,198 = $ 17,7590,45(Final Total $)

Thanks..

Re: Exclude total for one date

Frank_Hartmann — Thu, 04 Aug 2016 11:02:16 GMT

what if you put:

=if(Date>'$(vMonthEnd)' and Date <='$(vCurrMonth)',Date)

Re: Exclude total for one date

sunny_talwar — Thu, 04 Aug 2016 11:02:34 GMT

May be you need to use Aggr function to fix this:

Sum({<Division -= {.......}>}Aggr(Sum(Product$), Division, Day))

Add the set analysis to exclude 7/31/2016

Question: Your date field is called Division?

Re: Exclude total for one date

nareshthavidishetty — Thu, 04 Aug 2016 11:05:03 GMT

Yes,division is my date.

Thanks..

Re: Exclude total for one date

nareshthavidishetty — Thu, 04 Aug 2016 11:06:07 GMT

where division =if(Date>='$(vMonthEnd)' and Date <='$(vCurrMonth)',Date),which is an calculated dimension.

Thanks..

Re: Exclude total for one date

trdandamudi — Thu, 04 Aug 2016 11:10:00 GMT

Change your vMonthEnd as below:

= (MonthStart(addmonths(Date(today(),'MM/DD/YYYY'),-0)))

So that it will start from 08/01/2016.

Re: Exclude total for one date

sunny_talwar — Thu, 04 Aug 2016 11:14:04 GMT

Do you only want the total to change or do you want to get rid of the complete row?

Re: Exclude total for one date

nareshthavidishetty — Thu, 04 Aug 2016 11:17:07 GMT

Hi ,

I want to show the data from last of month end to till date but i need to exclude the last day of month end value in total.

Ex: Need to show data in the chart from 31/8/2016 to till date but the total should be from 1/8/2016 to tilldate.

Thanks..

Re: Exclude total for one date

Anil_Babu_Samineni — Thu, 04 Aug 2016 11:17:30 GMT

Can you please post sample Attachment

Use Expression some thing

Sum({<Date = {*} >}Product & '$') - Sum({<Date = {"07/31/2016",''MM/DD/YYYY'} >}Product & '$')

Sum({<Date -= {"07/31/2016",''MM/DD/YYYY'} >}Product & '$')

Re: Exclude total for one date

sunny_talwar — Thu, 04 Aug 2016 11:20:52 GMT

I think this is what you need:

Sum({<Division -= {.......}>}Aggr(Sum(Product$), Division, Day))

If you are unable to make it work, would you be able to provide a sample?

Re: Exclude total for one date

nareshthavidishetty — Thu, 04 Aug 2016 11:21:54 GMT

Hi Sunny,

I have used the division and day as below

division =if(Date>='$(vMonthEnd)' and Date <='$(vCurrMonth)',Date)

Day =pick(match(WeekDay(Date),'Mon','Tue','Wed','Thu','Fri','Sat','Sun'),'Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday')

is this logic Sum({<Division -= {.......}>}Aggr(Sum(Product$), Division, Day)) will work?

Thanks..

Re: Exclude total for one date

sunny_talwar — Thu, 04 Aug 2016 11:23:24 GMT

Yes, I am almost certain it will, just not sure how your data is to provide suggestion related to the set analysis.

Re: Exclude total for one date

nareshthavidishetty — Thu, 04 Aug 2016 11:33:24 GMT

Can you please provide the full expression which includes division and day dimensions mentioned above.Actually am got little bit of confuse.

Thanks..

Re: Exclude total for one date

sunny_talwar — Thu, 04 Aug 2016 12:13:30 GMT

Would you be able to provide a sample to help us help you better

Re: Exclude total for one date

nareshthavidishetty — Thu, 04 Aug 2016 12:17:19 GMT

yeah sure but how to attach document in this.

Thanks..

Re: Exclude total for one date

sunny_talwar — Thu, 04 Aug 2016 12:27:11 GMT

Look here:

Uploading a Sample

Re: Exclude total for one date

nareshthavidishetty — Thu, 04 Aug 2016 12:35:19 GMT

Hi Sunny,

Please find attached spread sheet.In qlikview we derived Division and Day as Calculated Dimension.

Thanks..

Re: Exclude total for one date

sunny_talwar — Thu, 04 Aug 2016 12:39:49 GMT

Would it be possible to provide the raw data behind this output? or provide the sample application you have been working with? If confidentiality is a concern, you can look here:

Preparing examples for Upload - Reduction and Data Scrambling

Re: Exclude total for one date

nareshthavidishetty — Thu, 04 Aug 2016 13:32:26 GMT

Hi Sunny,

Attached is the same app.

Thanks..

Re: Exclude total for one date

vinieme12 — Thu, 04 Aug 2016 13:47:27 GMT

Frank has already pointed out the problem in your Expression

You vMonthend is 7/31/2016

if you want to exclude this date then you should be comparing as just Greater than and NOT as Greater than equal to which then includes your monthend date. Just remove the equal sign

=if(Date>'$(vMonthEnd)' and Date <='$(vCurrentmonth)',Date)