topic Re: Logic in QlikView

Logic

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

Hi ALl,

Please find the attachment of the qvw file .

Here i have bar chart which has the dimesnion defectsummaryreportperiod this is statring form NOV ..

The requirement is like it should show the 13 months from the selected date from Orderdate and the date format is MM-DD-YYYY.

say for example now i have selcted 11-11-2017.....that means and if you look at the bar chart it is showing 13 months charts...starting from NOV 2016 to NOV 2017..

but if i select October it will show 12 months... the starting will still be NOv..

Please help me on this ...If i slect in future Dec from Orderdate then the bar chart should start from Dec... so that it will be 13 months ..

How can ia chive this ...

Thanks in advance

Re: Logic

amarnath_krishn — Tue, 21 Nov 2017 10:42:52 GMT

Currently the data is available only from Nov-2016 and ends by Oct-2017.

I would suggest to create a variable to identify the start range for the rolling 13-month and use that variable in all the expressions in set analysis to limit the data.

Re: Logic

smilingjohn — Tue, 21 Nov 2017 10:51:30 GMT

hi Amarnath..

Can you please help me with this ..

It will be really great..

Say for example consider it for 6 months ......

Thanks in advance

Re: Logic

stabben23 — Tue, 21 Nov 2017 10:52:42 GMT

You could try to add these set analysis into Your Expressions or simular:

Sum ({<DSLoadDate={">=$(=date(addmonths(DSLoadDate,-13),'MMM-YYYY'))<=$(=max(date(addmonths(DSLoadDate,0)),'MMM-YYYY'))"}>}DSCreateCount)

Re: Logic

amarnath_krishn — Tue, 21 Nov 2017 10:59:34 GMT

In Expresssions:

Sum ({<DefectSummaryReportPeriod={">$(vChartRangeStart) <=$(vChartRangeEnd)"}>}DSCreateCount)

where variable - vChartRangeStart=Date(AddMonths(DSLoadDate,-6),'MMM-YYYY')

vChartRangeEnd=Date(AddMonths(DSLoadDate,0),'MMM-YYYY')

Re: Logic

smilingjohn — Tue, 21 Nov 2017 11:15:55 GMT

Amarnath this is not working ..

I followed the steps you suggested ..

I dont know where i am going wrong ...

Re: Logic

smilingjohn — Tue, 21 Nov 2017 11:16:30 GMT

Hi Staffon thanks for the reply

this is not working ..i followed what you suggested ..

Re: Logic

amarnath_krishn — Tue, 21 Nov 2017 11:22:43 GMT

Please share the updated qvw; let me have a look.

Re: Logic

stabben23 — Tue, 21 Nov 2017 11:25:51 GMT

Ok, than you have to find out what not is working, because it Works for me.

Re: Logic

smilingjohn — Tue, 21 Nov 2017 11:32:44 GMT

Hi Amarnath .

Please find the attachment of the qvw file where i have uodated the variable as per your suggestions.

Thanks

Re: Logic

smilingjohn — Tue, 21 Nov 2017 11:40:32 GMT

Hi Staffan ..

How is is working for you ? r u using my file the one i attached as test2 ?

if yes then can you please send it to me

Re: Logic

stabben23 — Tue, 21 Nov 2017 11:46:11 GMT

Like this?

Re: Logic

amarnath_krishn — Tue, 21 Nov 2017 11:48:45 GMT

Re: Logic

amarnath_krishn — Tue, 21 Nov 2017 11:49:35 GMT

Just made the below corrections on your version:

1. add an equal sign before the variable definition

2. Date format is 'MMM-YYYY' for the variables

Re: Logic

smilingjohn — Tue, 21 Nov 2017 11:50:19 GMT

Hi Staffon..

If i tried to test the same thing for 8 months or 6 months it doesnot work ...

can you please help me with this ?????

Re: Logic

HirisH_V7 — Tue, 21 Nov 2017 11:54:03 GMT

Hi,

Please check out, using this expression,

Sum ({<DefectSummaryReportPeriod={">=$(=Date(MonthStart(Max(DSLoadDate), -12),'MMM-YYYY'))<=$(=Date(Max(DSLoadDate),'MMM-YYYY'))"}>}DSCreateCount)

PFA,

Hirish

Re: Logic

smilingjohn — Tue, 21 Nov 2017 11:57:32 GMT

Hi Amarnath..

This is changing the values..

I am getting all wron figures..

this is giving me the output ..but including this in an variable the values are something very wrong

The output is wrong for PROGRESS which is a runing total ..

Other than that the open and close are showing correct values

Re: Logic

smilingjohn — Tue, 21 Nov 2017 12:05:58 GMT

Hi Harish..

Thanks for the response..

If you see the this is giving wrong out for the 3rd expression which is a running total ..

other than this other is working ..

How do i handle this with the third expression which is a running total that is Progress

Thanks

Re: Logic

stabben23 — Tue, 21 Nov 2017 12:14:27 GMT

aha, change here, add DefectSummaryReportPeriod instead of DSLoadDate

Sum ({<DefectSummaryReportPeriod={">=$(=date(addmonths(DSLoadDate,-6),'MMM-YYYY'))<=$(=max(date(addmonths(DSLoadDate,0)),'MMM-YYYY'))"}>}DSCreateCount)

Re: Logic

smilingjohn — Tue, 21 Nov 2017 12:48:35 GMT

Hi staffan

Thanks third expression is giving wrong values which is a running total...

The other two are correct ..

How to deal with the running total expression