topic Re: In Qlikview, Find no of stores that have achieved the budget in QlikView

In Qlikview, Find no of stores that have achieved the budget

mayuranp — Mon, 21 Aug 2017 12:15:15 GMT

In Qlikview, I am trying to get no of stores that have achieved budget for this year to date.

I run the report every day morning for the year upto end of yesterday.

The expression below is not working due to double quotes within double quotes.

I tried replacing inner double quotes with single quotes but no luck.

Any suggestions / ideas?

count({<StoreNo={"=sum( {<DayNo={"<=$(vYesterday_DayNo)"}>}SalesAmount)> sum( {<FYea

Re: In Qlikview, Find no of stores that have achieved the budget

Anil_Babu_Samineni — Mon, 21 Aug 2017 15:33:21 GMT

Single Quote should work, May be share full expression?

Re: In Qlikview, Find no of stores that have achieved the budget

mayuranp — Mon, 21 Aug 2017 17:01:29 GMT

This is full expression

=count({<StoreNo={"=sum( {<SalesEntryType={'Sales'},FYear={$(vYesterday_Year)},FPeriod={$(vYesterday_PeriodNo)},DayNo={<=$(vYesterday_DayNo)}>}SalesAmount)<

sum( {<FYear={$(vYesterday_Year)},FPeriod={$(vYesterday_PeriodNo)},DayNo={'<=$(vYesterday_DayNo)'}>}[Budget Amount])"}>}StoreNo)

Returns 0 always

Re: In Qlikview, Find no of stores that have achieved the budget

mayuranp — Mon, 21 Aug 2017 17:18:53 GMT

I create the variables in script and then use them in chart expressions.

When I write the full expression in the chart, it works as you mentioned. However, storing the the expression in the variable for this is not working.

Anyway, I have a workaround now.

Here is the full expression that works in the chart:

=count({<StoreNo={"=sum( {<SalesEntryType={'Sales'},FYear={$(vYesterday_Year)},FPeriod={$(vYesterday_PeriodNo)},FYearName,Date,DayNo={'<=$(vYesterday_DayNo)'}>}SalesAmount)>

sum( {<[Gen Prod Posting Group Code],[Misc Brands],[Dont Show on Analysis Screen],[Hour],FYear={$(vYesterday_Year)},FPeriod={$(vYesterday_PeriodNo)},FYearName,Date,DayNo={'<=$(vYesterday_DayNo)'}>}[Budget Amount])"}>}StoreNo)

Can this be stored as a variable in script?

Thanks.

Re: In Qlikview, Find no of stores that have achieved the budget

Kushal_Chawda — Mon, 21 Aug 2017 17:22:14 GMT

you can user single quote in inner set

count({<StoreNo={"=sum( {<DayNo={'<=$(vYesterday_DayNo)'}>}SalesAmount)> sum( {<FYea

Re: In Qlikview, Find no of stores that have achieved the budget

its_anandrjs — Mon, 21 Aug 2017 17:54:16 GMT

Put Equals symbol before the variable name and with different expression as well as try

count({<StoreNo={"=sum( {<DayNo={"<=$(=vYesterday_DayNo)"}>}SalesAmount)> sum( {<FYea

count({<StoreNo={"=sum( {<DayNo={'<=$(=vYesterday_DayNo)'}>}SalesAmount)> sum( {<FYea

Re: In Qlikview, Find no of stores that have achieved the budget

Anil_Babu_Samineni — Tue, 22 Aug 2017 00:48:05 GMT

If you create variables in script. you have to define = sign of before to the variable in set operator